ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Conditional formatting - why doesn't this work? (https://www.excelbanter.com/excel-programming/418003-conditional-formatting-why-doesnt-work.html)

DDawson
Conditional formatting - why doesn't this work?
 
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

Barb Reinhardt
Conditional formatting - why doesn't this work?
 
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.



"DDawson" wrote:

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

DDawson
Conditional formatting - why doesn't this work?
 
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

DDawson
Conditional formatting - why doesn't this work?
 
Can someone please help me with this. Thanks.

"DDawson" wrote:

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


All times are GMT +1. The time now is 07:34 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com