| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting - Not all colors work? | New Users to Excel | |||
| Will Conditional Formatting work? | Excel Worksheet Functions | |||
| Will Conditional Formatting work? | Excel Worksheet Functions | |||
| Conditional Formatting work automatically | Excel Programming | |||
| Conditional Formatting Work around? | Excel Worksheet Functions | |||
Linear Mode
