hiding rows based on conditional format of background colour

[fry]

Hi All

I'm struggling with this. I found this code that seemed to be what I want but when I ran it, it did absolutely nothing?????

Sub test() 
Dim LR As Long      'Last Row 
Dim RN As Integer   'Row Number 

LR = Cells(Rows.Count, "J").End(xlUp).Row 

Application.ScreenUpdating = False 

    For RN = 1 To LR 
    If Cells(RN, "J").Interior.ColorIndex = 37 Then Cells(RN, "J").EntireRow.Hidden = True 
    Next RN 
    
 Application.ScreenUpdating = True 
  
End Sub

I have an increasing number of rows that are normally formatted unless a value is entered in a particular cell, in which case the whole row is formatted in a different colour. I need a bit of code that will hide all the rows in that colour....

Anyone like to help with this???

 

[Norie]

Why do you need code?

Couldn't you just use a filter with the criteria based on that used for the conditional formatting?

PS The reason the code doesn't work is because conditional formatting doesn't really change the color.

 

[Datsmart]

Change your code to look at the same condition as your conditional format.
Like Norie said, Conditional formating does not set a property in the cell that can be used outside of conditional format, it just changes the view.

 

[fry]

Hi Norie

I've managed to sort it another way........BUT............I now can't get the hidden rows back???

Help....

Here's the code that works

Sub test()
Dim LR As Long      'Last Row
Dim RN As Integer   'Row Number

LR = Cells(Rows.Count, "B").End(xlUp).Row

Application.ScreenUpdating = False

    For RN = 1 To LR
    If IsEmpty(Cells(RN, "J")) = False Then Cells(RN, "J").EntireRow.Hidden = True
    Next RN
    
 Application.ScreenUpdating = True
  
End Sub

 

[fry]

Phew........panic over..........I changed 'false' to 'true' when I should have changed 'true' to 'false'...........DOH!!!!

Thanks for your help anyway...