Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: simlple excel macro

    Hello all,

    I want to build a macro in excel that will scan the unempty cells and color them accordingly.
    e.g. if a cell contains the word 'hello' it will color it yellow etc.

    any ideas?

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Try this:

    Code:
    Sub ColorCell()
    
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
        With Selection.Interior
            .ColorIndex = 36
            .Pattern = xlSolid
        End With
    End Sub
    Change color index as needed.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Welcome to the Board!

    You could also use Conditional Formatting-->Cell Value Is-->Equal To-->="Hello" Then Format-->Patterns-->Yellow.

    Hope that helps,

    Smitty

  4. #4
    Join Date
    Apr 2004
    Posts
    1

    Question Hiding rows

    Hello all

    I am new to macros in excel. I have a column that is currently empty but will be inputted with dates soon.

    What i want to do is have two macros: one will go through the entire spreadsheet and select the rows that have a date in the aforementioned column cell AND hide that particular row. The other will do the opposite - it will unhide the rows that were previously hidden.

    please help

    y

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Re: Hiding rows

    ok

    Here are 2 macros for you

    Code:
    Sub Macro1()
    'hides rows that contain numbers
        Range("A1").EntireColumn.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Hidden = True
    End Sub
    
    Sub Macro2()
    'unhides all rows
        Cells.EntireRow.Hidden = False
    End Sub
    Macro1 hides all rows containing numbers
    as Excel interprits dates as numbers then these rows will be hidden
    it looks for numbers in column A

    HTH

    David

    macro2 unhides all hidden rows on the worksheet

  6. #6
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    Select Case

    Yaron,

    You can also try Select Case in VBA. Something like...
    sub ColorCells()
    Do
    Select Case ActiveCell.Value
    Case "Hello"
    'using shades code.
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    With Selection.Interior
    .ColorIndex = 36
    .Pattern = xlSolid
    End With
    Case ""
    'Do Nothing or hide.
    Case "Good-Bye"
    'Do somthing else.
    Case Else
    'Catch everything else...do something...do nothing.
    End Select
    ActiveCell.offset(1,0).select
    Loop Until (some end condition)
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •