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 / New Users / September 2007

Tip: Looking for answers? Try searching our database.

It's Conditional Formatting, Jim, but not as we know it...

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Risky Dave - 25 Sep 2007 12:02 GMT
Hi,

Is there any way of producing the same as a conditional format on a cell
(ie. chenge fill and font colours according to a calculated value) but for
more than three (four, if you count the default) values?

TIA

Dave
Bob Phillips - 25 Sep 2007 12:32 GMT
An example

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

   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 1: .Interior.ColorIndex = 3    'red
               Case 2: .Interior.ColorIndex = 6    'yellow
               Case 3: .Interior.ColorIndex = 5    'blue
               Case 4: .Interior.ColorIndex = 10   'green
           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

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi,
>
[quoted text clipped - 5 lines]
>
> Dave
Risky Dave - 25 Sep 2007 13:02 GMT
Bob,

Many thanks for your prompt response.

Guess that means I'll have to do some work now :-)

Dave

> An example
>
[quoted text clipped - 34 lines]
> >
> > Dave
Bob Phillips - 25 Sep 2007 14:28 GMT
You could try this http://www.xldynamic.com/source/xld.CFPlus.Download.html

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Bob,
>
[quoted text clipped - 44 lines]
>> >
>> > Dave
Risky Dave - 25 Sep 2007 15:46 GMT
Outstanding. My thanks

> You could try this http://www.xldynamic.com/source/xld.CFPlus.Download.html
>
[quoted text clipped - 46 lines]
> >> >
> >> > Dave

Rate this thread:






 
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



©2008 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.