help me set up a Event Macros

  • Thread starter Thread starter jladika
  • Start date Start date
J

jladika

i am looking to use more than 3 colors in my spreadsheet
i can do the conditional formatting for 3 colors but need help
to use 5 colors.
I am new to this so please tell me how to do this
thank
 
You could use a worksheet_change event:

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

Select Case LCase(Target.Value)
Case Is = "val 1": myColor = 34
Case Is = "val 2": myColor = 33
Case Is = "val 3": myColor = 32
Case Is = "val 4": myColor = 31
Case Else
myColor = xlNone
End Select

Target.Interior.ColorIndex = myColor

End Sub

Adjust the colors to what you want. Add as many tests as you need. And adjust
the range (I used column A).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you want to learn more about what events are:

Chip Pearson has some notes about events at:
http://www.cpearson.com/excel/events.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm
 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

Select Case LCase(Target.Value)
Case Is = "val 1": myColor = 34
Case Is = "val 2": myColor = 33
Case Is = "val 3": myColor = 32
Case Is = "val 4": myColor = 31
Case Else
myColor = xlNone
End Select

Target.Interior.ColorIndex = myColor

End Sub

'==========
everything from the "option explicit" to the "End Sub" should be copied|pasted.

But you've got some more work to do.

You'll have to add the values that you want to check and put the colors that you
want for each value.
 
thanks for your help

when you say values
does that mean the cells i need to be colored?

i need a column for this to be done i
 
This portion:

Case Is = "val 1": myColor = 34
Case Is = "val 2": myColor = 33
Case Is = "val 3": myColor = 32
Case Is = "val 4": myColor = 31

had colorindexes chosen almost at random. I'd be willing to bet that you
wouldn't want to use 31-34 in your real code.

You could either record a macro when you set the colors of some test cell to see
what the numbers are or maybe visit David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/colors.htm

And besides, I only use 4 values and 4 colors!
 
Back
Top