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 / April 2007

Tip: Looking for answers? Try searching our database.

Conditional formatting macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
K1KKKA - 27 Apr 2007 13:29 GMT
Help.

I would like to know if it is possible to have a macro that changes
the colour of a cell dependant on the number or text within.

eg
If the following cells were populated as follows

a1=1 Make the cell Green

   =2 Make the cell Red

   =3 Make the cell Blue

   =4 Make the cell Orange and so on till 7

Is this at all possible ?
I know that i can colour cells 3 times with simple conditional
formatting, but would like to run upto seven different colours.

any help would be appreciated.

Steve
Bob Phillips - 27 Apr 2007 14:57 GMT
'-----------------------------------------------------------------
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 = 10   'green
               Case 2: .Interior.ColorIndex = 3    'red
               Case 3: .Interior.ColorIndex = 5    'blue
               Case 4: .Interior.ColorIndex = 6    'yellow
'etc.
           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)

> Help.
>
[quoted text clipped - 19 lines]
>
> Steve
 
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.