Highlight/ Conditional Formatting Macro

[sethmeister21]

Hi All, I've hacked together a macro that looks at my sheet and loops down highlighting certain criteria on the way e.g. if it says "Bankruptcy" or "Failure" it highlights the whole row with colour 42. Great. However, this works fine until I hit a blank cell...then its stops! I need it to keep going even if it hits a blank cell. The max amount of rows will be 32,000 so if I can set a Range (something like D1:D32000) this may be more efficient?

Anyway the main issue is to keep the Macro running even after the blanks until it has reached the last active cell. Please help! I've tried a few things with no success.

Sub Highlight_Event()
Do Until IsEmpty(ActiveCell)
Select Case ActiveCell.Text
Case "Bankruptcy"
ActiveCell.EntireRow.Interior.ColorIndex = 42
Case "Failure"
ActiveCell.EntireRow.Interior.ColorIndex = 42
Case "Monkey"
ActiveCell.EntireRow.Interior.ColorIndex = 42
Case "Tennis"
ActiveCell.EntireRow.Interior.ColorIndex = 42
Case "Hotmail"
ActiveCell.EntireRow.Interior.ColorIndex = 42
Case ""
ActiveCell.EntireRow.Interior.ColorIndex = -4142
End Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub​

 

[VoG]

Try

Sub Highlight_Event()
Dim LR As Long, i As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("D" & i)
        Select Case .Value
            Case "Bankruptcy", "Failure", "Monkey", "Tennis", "Hotmail": .EntireRow.Interior.ColorIndex = 42
            Case "": .EntireRow.Interior.ColorIndex = xlNone
        End Select
    End With
Next i
End Sub

 

[Jonmo1]

In case it wasn't clear, it stops at a blank cell because you told it to...

Do Until IsEmpty(ActiveCell)

Just making sure that was cleaar...

anyway, a very common code to use is to determine the last used row #.
then instead of Do Until.... Use for i = 1 to lastrow


Also, you're only applying 1 color, based on 5 different possible values.
Those 5 possible values can all be put in one case

Try this

Sub Highlight_Event()
Dim MyCol As Long, LR As Long, i As Long
MyCol = ActiveCell.Column
LR = Cells(Rows.Count, MyCol).End(xlUp).Row
For i = 1 To LR
    Select Case Cells(i, MyCol).Value
        Case "Bankruptcy", "Failure", "Monkey", "Tennis", "Hotmail"
            Rows(i).EntireRow.Interior.ColorIndex = 42
        Case Else
            Rows(i).EntireRow.Interior.ColorIndex = -4142
    End Select
Next i
End Sub

 

[Jonmo1]

Furthermore, since you're only applying 1 color, based on 5 different possible values, you don't need VBA.
This can be done with plain old Conditional Formatting.

This is a very common misunderstanding of the limitations of conditional formatting.
CF is NOT limited to 3 Conditions.
It is limited to 3 Formats.

So a Conditional Formatting Formula for this scenario could be

=OR(A1={"Bankruptcy","Failure","Monkey","Tennis","Hotmail"})

That's 5 possible conditions, but only 1 format applied.


Hope that helps..

 

[sethmeister21]

Hi VoG and jonmo1 - thanks so much for your swift replies. I tested out both sets of code and it worked fine. Jonmo1 - yes I realised I had inserted "IsEmpty(ActiveCell)" but it worked well at the time...before the data changed....then when I started getting empty cells it failed!

With regards to Conditional Formatting, it causes issues with file size (Excel Bloat) when you apply CF all the way through the spreadsheet so using a macro helps to reduce file size. Especially when using Excel 2003 - I should have mentioned.

Thanks guys!