Excel Copying Conditional Formatting in Excel

[DomElmfield]

Hi,

I am currently working on a spreadsheet which is using a traffic light system which is going to highlight fields a certain colour once a certain amount of days have been reached.

I have managed to get one line of this working, but is there a way to copy and paste this to the entire sheet - because everytime I attempt to do this the formatting will continue to relate to the original rule

The 3 formulars I'm using for the traffic light system are:

A4=$E$4<10
A4=IF(AND($E$4>10,$E$4<20),$E$4,"")
A4=$E$4>20

I have been able to copy this so it fills in the rows A4:E4 but this formatting needs to go all the way down to A83:E83 so that each row works independent of each other.

Currently when I copy and paste the format whatever I input into E4 affects the entire spread sheet while if I input a date into E10 it has no affect on its row or any of the spreadsheet whatsoever.

I am sure this is something very simple I am overlooking

Please help...it is driving me mad!

 

[Stoneboysteve]

DomElmfield,

Here is a macro I use to apply formatting. It is handy because it is not limited to the three conditional options, can be used on any range you add- edit in the code, and uses the change event for the worksheet so each time you initiate the change event it checks and updates the format.
I did not make any edits related to your case, you can edit the if statements to fit your needs. You can also add additional statements if needed. The macro is only an example of the syntax to perform the formatting.

Macro:
Sub Worksheet_Change(ByVal Target As Range)
Set myplage = Range("B2:F14")
For Each cell In myplage

If cell.Value = "1" Then
cell.Interior.ColorIndex = 3
End If
If cell.Value = "2" Then
cell.Interior.ColorIndex = 46
End If
If cell.Value = "3" Then
cell.Interior.ColorIndex = 6

End If
If cell.Value = "4" Then
cell.Interior.ColorIndex = 4
End If

If cell.Value <> "1" And cell.Value <> "2" And cell.Value <> "3" And cell.Value <> "4" Then
cell.Interior.ColorIndex = xlNone
End If

Next
End Sub

You could also use case statements, but that is another thread.

Stoneboysteve