PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Conditional formatting - why doesn't this work?

Reply
Thread Tools Rate Thread

Conditional formatting - why doesn't this work?

 
 
DDawson
Guest
Posts: n/a
 
      2nd Oct 2008
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
 
Reply With Quote
 
 
 
 
Barb Reinhardt
Guest
Posts: n/a
 
      2nd Oct 2008
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
>
>
 
Reply With Quote
 
DDawson
Guest
Posts: n/a
 
      3rd Oct 2008
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 With Quote
 
DDawson
Guest
Posts: n/a
 
      7th Oct 2008
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 With Quote
 
 
 
Reply

« Excel vs Lotus [pop-up screen with directories] | Reading from a folder »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional Formatting - Worked - Bad - Doesn't Work Victor Microsoft Access Form Coding 6 14th May 2010 11:26 PM
Conditional Formatting macro: simple, but doesn't work JP Microsoft Excel Programming 6 18th Feb 2010 10:42 AM
Conditional formatting doesn't want to cooperate Pierre Microsoft Excel Worksheet Functions 3 4th Nov 2008 10:22 PM
Conditional Formatting Doesn't Update =?Utf-8?B?QkNT?= Microsoft Access Forms 1 11th Dec 2005 09:51 PM
locate duplicates by sing conditional formatting--doesn't work for =?Utf-8?B?a2V3YXdvbWFu?= Microsoft Excel Misc 2 1st Sep 2005 01:39 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 03:55 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top