Colour font or background if cells =

[thedeadzeds]

Hi All,
I've tried using condtional formatting but as you know it only gives us 3 conditions. I just need anything in cells A1 to I500 to turn red if any of the following is NOT in the cell:

3:45
7:30
11:15
15:00

So basically, if any of the cells DO NOT include the above, turn the cell red.

Many thanks

 

[Trevor G]

Try this out.

Sub col()
Dim cell As Range
For Each cell In Range("A1:I500")
If cell.Value <> 3:45 Or cell.Value <> 7:30 Or cell.Value <> 11:15 Or cell.Value <> 15:00 Then
cell.Interior.Color = vbRed
End If
Next
End Sub

 

[geep1980]

if the cell properties are in Time then you can use this marco

Sub Color_Cells()
'
For Each bCell In Range("A1:I500")

    If bCell <> TimeSerial(3, 45, 0) And bCell <> TimeSerial(7, 30, 0) And bCell <> TimeSerial(11, 15, 0) And bCell <> TimeSerial(15, 0, 0) Then
    'MsgBox bCell.Address
    Range(bCell.Address).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Else
    
    End If
    Next bCell

End Sub

 

[thedeadzeds]

Thanks guys, it doesnt seem to work for some reason. Am I missing something from the code?

 

[Trevor G]

Do you have conditional formatting applied? If so remove it.

 

[mikerickson]

Conditional Formatting worked for me, with the formula

=ISERROR(FIND(TEXT(C1,"h:mm")&"~", "3:45~7:30~11:15~15:00~"))

 

[Rick Rothstein]

Conditional Formatting worked for me, with the formula

=ISERROR(FIND(TEXT(C1,"h:mm")&"~", "3:45~7:30~11:15~15:00~"))

That formula will fail for entries of 1:15 and 5:00. This modification to your formula should do the job though...

=ISERROR(FIND("~"&TEXT(C1,"h:mm")&"~", "~3:45~7:30~11:15~15:00~"))

 

[thedeadzeds]

Works like a treat, thanks very much

 

[thedeadzeds]

Sorry guys, still getting errors as its now highlihting some zeros