Conditional formatting - why doesn't this work?

D

DDawson

Please help, I'm stumped.

I've set up a worksheet calculate macro to colour certain rows based on the
contents of the adjacent cells, but one of the specifications isn't working
and I've tried everything.

This spec works:
'Highlight if later than 90 days since request
ElseIf myCE.Value < Now - 90 And myCE.Offset(0, 1).Value = "" _
And myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value _
= "Requester/PM" And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 22 'light red
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 22

But this one doesn't:

ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = And _
"Requester/PM" And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 36 'light yellow
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 36

Here is the full code:

Private Sub Worksheet_Calculate()
Dim myCE As Range
Dim WatchRange1 As Range

'Application.ScreenUpdating = False

Set WatchRange1 = Range("BQDate")

For Each myCE In WatchRange1

If myCE.Value = "" And _
myCE.Offset(0, -4).Value = "No" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 0
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 0
'Highlight Power Company if later than 90 days since request
ElseIf myCE.Value >= "" And _
myCE.Offset(0, -4).Value = "Yes" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 16
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 16
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 15
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 15
ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value =
"Requester/PM" And _
myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 36 'light yellow
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 36
ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = "Power
Company" And _
myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 36 'light yellow
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 36
ElseIf myCE.Value < Now - 90 And myCE.Offset(0, 1).Value = "" _
And myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = "Power
Company" _
And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 22 'light red
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 22
'Highlight Requester/PM if later than 90 days since request
ElseIf myCE.Value < Now - 90 And myCE.Offset(0, 1).Value = "" _
And myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value =
"Requester/PM" _
And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 22 'light red
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 22

Else
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 'black
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 'black
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 0 'blank
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 0 'blank
'0 Blank/Black
'3 Red
'36 Yellow
'15 Grey
'34 Light blue
'16 Dark grey
'
End If

Next myCE

'Application.ScreenUpdating = True

End Sub

Kind regards
Dylan
 
B

Barb Reinhardt

You have this

ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = And _
"Requester/PM" And myCE.Offset(0, -8).Value <> "Cancelled" Then

Take out the AND at the end of the 2nd line and try again.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.
 
D

DDawson

Sorry Barb, this was an error in my copy/pasting to the message when I
attempted to manually wordwrap it to make sense.

Here is my full code again and I've commented the parts that aren't working.
Can you please have another look at it for me? Thanks.

Private Sub Worksheet_Calculate()
Dim myCE As Range
Dim WatchRange1 As Range

Set WatchRange1 = Range("BQDate")

For Each myCE In WatchRange1

If myCE.Value = "" And _
myCE.Offset(0, -4).Value = "No" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 0
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 0

ElseIf myCE.Value >= "" And _
myCE.Offset(0, -4).Value = "Yes" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 16
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 16
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 15
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 15

'THIS PART DOESN'T WORK
ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value =
"Requester/PM" And _
myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 36 'light yellow
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 36

'AND THIS PART
ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = "Power
Company" And _
myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 36 'light yellow
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 36

'FROM HERE ON IS OKAY
ElseIf myCE.Value < Now - 90 And myCE.Offset(0, 1).Value = "" _
And myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = "Power
Company" _
And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 22 'light red
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 22
'Highlight Requester/PM if later than 90 days since request
ElseIf myCE.Value < Now - 90 And myCE.Offset(0, 1).Value = "" _
And myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value =
"Requester/PM" _
And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 22 'light red
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 22

Else
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 'black
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 'black
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 0 'blank
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 0 'blank

End If

Next myCE

End Sub
 
D

DDawson

Can someone please help me with this. Thanks.

DDawson said:
Sorry Barb, this was an error in my copy/pasting to the message when I
attempted to manually wordwrap it to make sense.

Here is my full code again and I've commented the parts that aren't working.
Can you please have another look at it for me? Thanks.

Private Sub Worksheet_Calculate()
Dim myCE As Range
Dim WatchRange1 As Range

Set WatchRange1 = Range("BQDate")

For Each myCE In WatchRange1

If myCE.Value = "" And _
myCE.Offset(0, -4).Value = "No" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 0
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 0

ElseIf myCE.Value >= "" And _
myCE.Offset(0, -4).Value = "Yes" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 16
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 16
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 15
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 15

'THIS PART DOESN'T WORK
ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value =
"Requester/PM" And _
myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 36 'light yellow
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 36

'AND THIS PART
ElseIf myCE.Value = "" And myCE.Offset(0, 1).Value < (Now - 60) And _
myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = "Power
Company" And _
myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 36 'light yellow
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 36

'FROM HERE ON IS OKAY
ElseIf myCE.Value < Now - 90 And myCE.Offset(0, 1).Value = "" _
And myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value = "Power
Company" _
And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 22 'light red
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 22
'Highlight Requester/PM if later than 90 days since request
ElseIf myCE.Value < Now - 90 And myCE.Offset(0, 1).Value = "" _
And myCE.Offset(0, -4).Value = "No" And myCE.Offset(0, 4).Value =
"Requester/PM" _
And myCE.Offset(0, -8).Value <> "Cancelled" Then
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 '
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 22 'light red
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 22

Else
Range(myCE, myCE.Offset(0, -17)).Font.ColorIndex = 0 'black
Range(myCE, myCE.Offset(0, 4)).Font.ColorIndex = 0 'black
Range(myCE, myCE.Offset(0, -17)).Interior.ColorIndex = 0 'blank
Range(myCE, myCE.Offset(0, 4)).Interior.ColorIndex = 0 'blank

End If

Next myCE

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

request help to combine two similar macros 4
Cell colour doesn't delete 6
Worksheet event to change formatting when date expires. 5
2 codes in one sheet 5
copy and paste problem 1
Two codes in one set. 1
Changing Font Color to White if Background is Black 2
Worksheet change code to colour in rows in a database 7

Top