Multiple Conditional Formatting rules

  • Thread starter Thread starter SST
  • Start date Start date
S

SST

Can anyone tell me how I can set 20 rules for conditional formatting o
one sheet?

What I need to do is colour a spreadsheet with 15-20 colours accordin
to a (different) spreadsheet full of numbers, so that eg 1 = red, 2
pink, 3 = blue, 4 = green..... etc...

Any help would be much appreciated.

Thanks
 
Hi
this can only be done with VBA. Put the following code in your
worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
Application.EnableEvents = False
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3
Case 2: .Inerior.ColorIndex = 10
'etc. add additional conditions
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

I'm not so sure what you meant with your different spreadsheet?. You
may have to change the range which is colored in the above code.
Currently the code checks the values in A1:A100 in formats these cells
 
Thank you, but i'm afraid I'm not familiar with VBA.

I managed to open the VBA editor window and pasted in your code.

What do I do next?

I clicked on "run sub", it brought up a window that made me create a
macro. When I click ok, it creates a line under the code, then it
says

sub CF()

End sub

Thanks.
 
Hi
this code has to go in the worksheet module. Try the following:
- open your workbook
- right click on the tab name
- in the context menu choose 'code'
- paste your code in the appearing VBA editor
- close the VBA editor and save your workbook

Now it should work. But you probably have to change the code below
according to your needs (range, color values, etc.). So if you're not
that familiar with VBA this could get tricky :-). You may have a look
at the following site to get started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Sorry to be slow....

I created a test workbook and populated Sheet 1 A1:A100 with 1s an
2s.

I then followed your instructions, pasted your code into the workshee
module, closed and saved it.

Nothing happens in the sheet of 1s and 2s...

Do I need to run something?

Regards,
Sara
 
Hi Sarah
That nothing happens on sheet 2 is o.k. as this code works worksheet
specific. Sheet 1 though should do something. If you like, mail me your
spreadsheet (frank[dot]kabel[at]freenet[dot]de) and I'll have a look at
it
 
Back
Top