| Home |
| Search |
| Today's Posts |
|
#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 |
| 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 | |||
Threaded Mode
