Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / November 2006

Tip: Looking for answers? Try searching our database.

Conditional Formatting with more than 3 conditions

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sara_Chase - 21 Jun 2006 08:37 GMT
I'm currently designing a worksheet that contains all of our department
tasks. Each task may have one of the following status: Open, On-going,
For Review / Approval, Verified, Closed, Pending, Rejected. Each of
this status has its own assigned background cell color (applies to the
whole row). I am looking a workaround to Excel's limit of 3 condition
to do this.

Any thoughts?

Signature

Sara_Chase

Bob Phillips - 21 Jun 2006 09:10 GMT
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

   On Error GoTo ws_exit:
   Application.EnableEvents = False
   If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
       With Target
           Select Case .Value
               Case "Open":
                       .Interior.ColorIndex = 3    'red
               Case "For Review / Approval":
                       .Interior.ColorIndex = 6    'yellow
               Case "Verified":
                       .Interior.ColorIndex = 5    'blue
               Case "Closed":
                       .Interior.ColorIndex = 10   'green
               Case "Pending":
                       .Interior.ColorIndex = 38   'rose
               Case "Rejected":
                       .Interior.ColorIndex = 37   'pale blue
           End Select
       End With
   End If

ws_exit:
   Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I'm currently designing a worksheet that contains all of our department
> tasks. Each task may have one of the following status: Open, On-going,
[quoted text clipped - 4 lines]
>
> Any thoughts?
Sara_Chase - 21 Jun 2006 09:29 GMT
Thanks! I'll try this out! I was hoping I need not code the workaround.
:)

Signature

Sara_Chase

Bob Phillips - 21 Jun 2006 09:40 GMT
Then you either have to wait for Office 2007, or implement this addin
http://www.xldynamic.com/source/xld.CFPlus.Download.html

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thanks! I'll try this out! I was hoping I need not code the workaround.
> :)
Sara_Chase - 21 Jun 2006 09:53 GMT
Hi.

I've tried the VB out and it works great. Is it possible to extend the
color to the rest of the row just like in the conditional formatting?

Thanks again!

Signature

Sara_Chase

Bob Phillips - 21 Jun 2006 10:55 GMT
Of course <g>

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

   On Error GoTo ws_exit:
   Application.EnableEvents = False
   If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
       With Target
           Select Case .Value
               Case "Open":
                       .Entirerow.Interior.ColorIndex = 3    'red
               Case "For Review / Approval":
                       .Entirerow.Interior.ColorIndex = 6    'yellow
               Case "Verified":
                       .Entirerow.Interior.ColorIndex = 5    'blue
               Case "Closed":
                       .Entirerow.Interior.ColorIndex = 10   'green
               Case "Pending":
                       .Entirerow.Interior.ColorIndex = 38   'rose
               Case "Rejected":
                       .Entirerow.Interior.ColorIndex = 37   'pale blue
           End Select
       End With
   End If

ws_exit:
   Application.EnableEvents = True
End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi.
>
> I've tried the VB out and it works great. Is it possible to extend the
> color to the rest of the row just like in the conditional formatting?
>
> Thanks again!
Sara_Chase - 22 Jun 2006 00:49 GMT
Hi again!

I've got one more question ... instead of the entire row, how do yo
set the color for selected cells only, say column 1 to 10?

Thanks in advance
Sara_Chase - 22 Jun 2006 01:17 GMT
I have a follow-up question ... so sorry ...

The code doesn't seem to work if one of the cell (computed) within the
row has a value. I have this "estimated due date" column wherein I
compute the date that the task is due based on the priority and the
date that the task was raised.

Signature

Sara_Chase

Bob Phillips - 22 Jun 2006 09:18 GMT
That is because a referenced cell change doesn't trigger the change event
for monitored cells. It can be circumvented by using the Calculate event to
trigger the code for any change, not efficient but it works

Const WS_RANGE As String = "H1:H10"

'-----------------------------------------------------------------
Private Sub Worksheet_Calculate()
'-----------------------------------------------------------------
Dim cell As Range
   For Each cell In Range(WS_RANGE)
       Call ColourMe(cell)
   Next cell
End Sub

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------

   On Error GoTo ws_exit:
   Application.EnableEvents = False
   If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
       Call ColourMe(Target)
   End If

ws_exit:
   Application.EnableEvents = True
End Sub

'-----------------------------------------------------------------
Private Sub ColourMe(ByRef Target As Range)
'-----------------------------------------------------------------
   With Target
       Select Case .Value
           Case "Open":
                   .Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 3  'red
           Case "For Review / Approval":
                   .EntireRow.Interior.ColorIndex = 6    'yellow
           Case "VerifiedParent.Cells(.Row, 1).Resize(, 10)"
                   .Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 5    'blue
           Case "Closed":
                   .Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 10   'green
           Case "Pending":
                   .Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 38   'rose
           Case "Rejected":
                   .Parent.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex
= 37   'pale blue
       End Select
   End With
End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I have a follow-up question ... so sorry ...
>
> The code doesn't seem to work if one of the cell (computed) within the
> row has a value. I have this "estimated due date" column wherein I
> compute the date that the task is due based on the priority and the
> date that the task was raised.
Sara_Chase - 23 Jun 2006 01:18 GMT
Many thanks!

Signature

Sara_Chase

Bob Phillips - 22 Jun 2006 09:11 GMT
'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

   On Error GoTo ws_exit:
   Application.EnableEvents = False
   If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
       With Target
           Select Case .Value
               Case "Open":
                   Me.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex = 3
'red
               Case "For Review / Approval":
                   Me.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex = 6
'yellow
               Case "Verified":
                   Me.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex = 5
'blue
               Case "Closed":
                   Me.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex = 10
'green
               Case "Pending":
                   Me.Cells(.Row, 1).Resize(, 10).Interior.ColorIndex = 38
'rose
               Case "Rejected":
                   Me.Cells(.Row, 1).Resize(, 10).ColorIndex = 37 'pale
blue
           End Select
       End With
   End If

ws_exit:
   Application.EnableEvents = True
End Sub

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Hi again!
>
> I've got one more question ... instead of the entire row, how do you
> set the color for selected cells only, say column 1 to 10?
>
> Thanks in advance!
Nir - 08 Nov 2006 21:46 GMT
Bob Hi,
1. it seems not to work value coming from a formula
2. when the value is removed it keeps the format

can you assist
thanks

> Of course <g>
>
[quoted text clipped - 37 lines]
> http://www.excelforum.com/member.php?action=getinfo&userid=35616
> > View this thread: http://www.excelforum.com/showthread.php?threadid=553946
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.