Highlight/ Conditional Formatting Macro

sethmeister21

New Member
Joined
Mar 10, 2010
Messages
20
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Mar 10, 2010
Messages
20
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!
 

Forum statistics

Threads
1,085,565
Messages
5,384,469
Members
401,903
Latest member
MarBer

Some videos you may like

This Week's Hot Topics

Top