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

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))

Cheers. That's worked out brilliantly

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