• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting in Excel 2003

jazzkid

jazzkid

Member
I need to use conditional formatting in a spreadsheet a client is using. Unfortunately they only have Excel 2003.

What I have is four different areas, lets say for ease of explanation, A, B, C, D and E and a range for each month of about 30 or 40 incidents. There can be any number in each of the areas. What I want to do - easy in Excel 2010 - is to highlight each area in a different colour. When I tried to do it, it was OK for say "A" and it coloured that yellow as I indicated in the conditional formatting. However, when I chose the range again and asked for "B" to be coloured red, it removed the yellow from "A" and only left "B" coloured red. I guess I need a formula - but don't really know where to start. Any suggestions would be greatly appreciated.
 
Ok, so conditional formatting in excel 2003 (and later versions?) gives you the option for three criteria. These are applied in order, and excel stops looking at the format for each cell when it's found a criteria that it matched.

In order to give a solution I think you're probably going to need to post a copy of your spreadsheet (see forum rules for how to do that if you're not sure).
 
Thanks GeorgeF211 wasn't sure whether to do that or not. I have put the document on https://www.dropbox.com/s/6k207p0n4xj1zfa/Incident%20report%20August.xls - this is what I got from the client - it hasn't been amended other than to change the names in the cells. The cells I need highlighted are in column E. Thanks for any assistance. As there are only three criteria available in 2003, can I highlight the five areas in different colours - I suspect not?? Would still be interested in how this could be done.
 
If you're happy using some VBA code, try this:

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Set CheckRange = Range("E3:E100") 'this is the range you're checking for a condition

For Each Cell In CheckRange

If UCase(Cell.Value) = "EKC" Then  'replace the = "Agression" part with whatever you want to format based on condition 1
Cell.Interior.ColorIndex = 3 'Colorindex 3 is Red
End If

If UCase(Cell.Value) = "PEAKE" Then
Cell.Interior.ColorIndex = 46 'colorindex 46 is Orange
End If

If UCase(Cell.Value) = "SCOTT" Then
Cell.Interior.ColorIndex = 6 'colorindex 46 is Yellow
End If

If UCase(Cell.Value) = "PEARCE" Then
Cell.Interior.ColorIndex = 4 'colorindex 4 is Green
End If

If UCase(Cell.Value) <> "EKG" And UCase(Cell.Value) = "PEAKE" And UCase(Cell.Value) = "SCOTT" And UCase(Cell.Value) = "PEARCE" Then
Cell.Interior.ColorIndex = xlNone 'This strips the cell colour if none of the conditions are met
End If

Next
End Sub
[/pre]
 
Thanks GeorgeF211. Will try it and see if I can get it to go. Will not have an opportunity to do this til the weekend.
 
You must log in or register to reply here.
Back
Top