highlight/remove highlight of rows conditional on cellvalue

**timtim89** · 03-30-2012, 09:29 AM

Hey there,

What I wish to acheive by this macro is that all rows with value in column B being the same as the chosen value from a dropdown list (in d2) are highlightet, when the macro is run. Also, when d2 is empty and the macro is run, I want the (by the macro) previously highlightet rows to go back to normal (not highlightet). Hope that makes sense

The code below (not working) shows what I got at this point.
I also considered making it a Worksheet_Change event instead, but i guessed this would make it more demanding than a sub to run by click on a commandbutton. Is that right?

Thanks a lot in advance for your help.

Sub Highlight()
    
    Dim lngRow As Long
    
    Application.ScreenUpdating = False
    lngRow = Range("B4").End(xlDown).Row
    Do While lngRow > 0
        If Range("d2").Value = "" Then
            Rows(lngRow).Interior.Pattern = xlNone
        Else if Cells(lngRow, 2).Value = Range("d2").Value Then
            Rows(lngRow).Interior.ColorIndex = 3
        
        End If
        lngRow = lngRow - 1
    Loop
    Application.ScreenUpdating = True
   
End Sub

**venkat1926** · 04-01-2012, 01:22 AM

try this macro
highlighting is ambiguous and also will go away if you accidentally select some other cell
I have indicated highlighting by a color

Sub test()
Dim x, r As Range, filt As Range, j As Long, k As Long
Worksheets("sheet1").Activate
x = Range("D2").Value
Set r = Range(Range("A3"), Cells(Rows.Count, "M").End(xlUp).Offset(0, 2))
'It would be more convenient if you do not leave blanks cells or rows
'while desining data sheets.
'MsgBox r.Address
r.Cells.Interior.ColorIndex = xlNone
If Range("d2") <> "" Then
r.AutoFilter field:=2, Criteria1:=x
Set filt = r.Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
j = filt.Areas.Count
For k = 1 To j
filt.Areas(k).Cells.Interior.ColorIndex = 6
Next k
End If
ActiveSheet.AutoFilterMode = False
End Sub