Conditional formatting and IF statements

I am unfamiliar with VBA and macros so this will probably be a bit too in depth for me.

Hi Darren,

Could you post the formula's you are using in conditional format + the forth one.
Also tell me which cells the conditional format is applied to.

Best regards,
Trowa

Hi Trowa

I am not using formulas. I have conditionally formatted a cell so that it changes colours based on the information that I am putting in manually from a previous column. So for example if i manually type into cell A1 82% I have conditionally formatted cell B2 to either change to red if it is below 82%. I have added a second conditional format which if B2 is =82% this will change to yellow as it is the same score and if it is anything above 82% the 3rd condition is to change the cell colour to show orange, The problem I encounter is if the previous score is less than 100% and then I get a score of 100% the cell colour changes to orange as it is an increase. What I want to happen is for any cells that contain 100% to change to green. I will only be using a certain range of cells for 10 customers over a 12 month period. I must admit I would call myself an amateur with Excel and googled if it was possible to add a 4th conditional format on Excel 2003 and this seemed to be the most useful forum out of the lot of them.

Hi Darren,

Basically anything with an = in front of your criteria means it is a formula.

So your situation is like:
Conditonal format is applied to B2.

1. =B2<A1    Color Red
2. =B2=A1    Color Yellow
3. =B2>A2    Color Orange
4. =B2=100%  Color Green

To make this work automatically right-click on the sheets tab and select view code.
Paste the following code in the big white field:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
End If
If Range("B2").Value < Range("A1").Value Then Range("B2").Interior.ColorIndex = 3
If Range("B2").Value = Range("A1").Value Then Range("B2").Interior.ColorIndex = 6
If Range("B2").Value > Range("A1").Value Then Range("B2").Interior.ColorIndex = 45
If Range("B2").Value = 1 Then Range("B2").Interior.ColorIndex = 10
End Sub

Now change the value in either A1 or B2 and see if the colorcoding works.

PS. I assumed you formatted the cells as Percentage.

Best regards,
Trowa

Sorry about all the messages.

I have now removed the conditional formatting and have used the codes you have provided and it works perfectly however I dont know what the colour code is for the lighter shade of green which is the colour I need (lime green). As the colour code you provided was a dark green. I am currently in work and having some network issues so would like to apologise if you are receiving duplicate messages from me

I also have a further question. The code you have provided I implemented it for just one cell to try and use it. I have cells that I need to use from varying areas on the spread sheet

Would I need to write 10 of these codes to allow me to do it for each cell down from the one above or is there a way of doing it to include all the 10 cells

On the sheet I am using I need to use the information to change the cell colour from cell F4 down to F13 from the information that is in cell C4 down to C13.

and also would I need to write 12 seperate codes to allow me to do it for the 12 seperate months or can I use the one code but add all the varying cell ranges

I assumed this would work but its causing problems

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F4:F13")) Is Nothing Then
If Intersect(Target, Range("C4:C13")) Is Nothing Then Exit Sub
End If
If Range("F4:F13").Value < Range("C4:C13").Value Then Range("F4:F13").Interior.ColorIndex = 3
If Range("F4:F13").Value = Range("C4:C13").Value Then Range("F4:F13").Interior.ColorIndex = 6
If Range("F4:F13").Value > Range("C4:C13").Value Then Range("F4:F13").Interior.ColorIndex = 45
If Range("F4:F13").Value = 1 Then Range("F4:F13").Interior.ColorIndex = 10
End Sub