Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    5

    Unanswered: VBA - Highlighting cells with a certain conditon

    Hi there,

    I'm pretty new to this VBA stuff, so any help would be greatly appreciated.

    I'm looking to use VBA to start at A1 and if the value in that cell is, say less than 5, then highlight red and then move down to B1 and keep going to the end of the list.

    I have the highlight part all sorted using

    With Selection.Interior
    .ColorIndex = 3
    .Pattern = xlSolid
    End With

    But it's the IF statement and then the moving down a cell I have a problem with.

    Would it be more effcient if there was a count to calculate how many rows there were in the worksheet before going onto the hihglighting part?

    Many thanks

    Colin

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    There's a file on your computer with the answer to your question (and many more). It's in your \Program Files\Microsoft Office\Office\Samples folder, named SAMPLES.XLS. Be sure to pay close attention to the Conditional Formatting sheet--this might be a better answer than VBA.

    You'll also need a construct to select the entire column of cells in the list by referencing Range("A1", Range("A1").End(xlDown))

  3. #3
    Join Date
    Feb 2004
    Posts
    5

    Talking

    Cheers. That's worked out brilliantly

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

    To do this in VBA try a Do...Loop Until

    Hi,

    Conditional Formatting should help you out, however, read the following if you want to do this in VBA.

    To try to do this in VBA, use a Do...Loop Until, with an line of code such as, Loop Until ActiveCell = ""

    Off the top of my head, you could do something like...

    Sub HighlightCells()

    Range("A1").select
    Do
    If Activecell.Value < 5 Then
    Selection.Interior.ColorIndex = 3
    End If
    Activecell.offset(1,0).select 'moves activecell down one row.
    Loop Until ActiveCell = ""

    End Sub


    Another thing you might want to do, in case you want to highlight with more than one color, is to use Select...Case.

    From the previous code, remove the IF Statement and the two lines of code after it and code...

    Select Case Activecell.Value
    Case <5
    Selection.Interior.ColorIndex = 3
    Case 5, 6, 7, 8, 9, 10
    Selection.Interior.ColorIndex = 6
    Case >10
    Selection.Interior.ColorIndex = 4
    Case Else
    'Do Nothing.
    End Select.

    You may need to do a Select Case to save nesting multiple IF statements and if you have more than 3 conditions you want to highlight for, because Conditional Formatting can only handle 3 different conditions.

    Enjoy!
    -Mike

Posting Permissions

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