Hide Row based on, if condition formatting = True ?

[lpking2005]

hi,

I have cell ("D43"), which has conditional formatting.

What I want is to Hide the Row (43), if the conditional formatting = true
But i cant seem to get it to work.

my current code is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'===================================================================
'Hide row 43 when Cell "Rotate" message is not visible
'===================================================================

If Range("D43").Condition1 = True Then
    Range(Rows(43)).Hidden = False
Else
    Range(Rows(43)).Hidden = True
End If

End Sub

 

[Jonmo1]

You're much better off testing for the condition that causes the CF to color the cell.

What is the formula in the conditional formatting?

 

[lpking2005]

the formula is:

=OR(OR($R$40>4,$Q$40>4,$P$40>4,$N$40>4,$M$40>4,$L$40>4,$J$40>4,$I$40>4,$H$40>45,$F$40>4,$E$40>4,$D$40>4),OR($R$35>10,$Q$35>10,$P$35>10),OR($N$35>29,$M$35>29,$L$35>29,$J$35>29,$I$35>29,$H$35>29,$F$35>29,$E$35>29,$D$35>29))


the formula is so long because its is working off about 24 cells.

What happens is if any of the 24 cells become red, then the conditional formatting for cell ("D43") activates.

 

[Jonmo1]

OK,

Several of those cells are contiguous. like P40:R40
And you don't need to test each individual cell.
You can test for the MAX value of All cells > whatever...

So that can be reduced to

=OR(MAX($D$40:$F$40,$H$40:$J$40,$L$40:$N$40,$P$40:$R$40)>4,MAX($P$35:$R$35)>10,MAX($D$35:$F$35,$H$35:$J$35,$L$35:$N$35)>29)


Also, what is in the intermediate cells, like say G40 K40 and O40 ?
If they are NOT numeric, then they can all be combined into one..

Like
=OR(MAX($D$40:$R$40)>4,MAX($P$35:$R$35)>10,MAX($D$35:$N$35)>29)


Then you can use these formulas in VBA using Evaluate

If Evaluate("=OR(MAX($D$40:$R$40)>4,MAX($P$35:$R$35)>10,MAX($D$35:$N$35)>29)") = TRUE Then..


Hope that helps.

 

[Roj47]

Would a conditional format to make the background white and the text white to give the impression it is hidden be sufficient, but allow you to see the information if the row is highlighted.