Excel Query

[cerbie1996]

I am doing a lot of work in excel and am wanting to automatically change the colour of the data whenpopulated.

What i want is to change the background colour if the valueof the cell is 1 tosay yellow; 2 to red; 3 to blue etc how can i do this automatically???

Thanks in advance

[catretriever]

Depending on how many variations you wanted you could use Conditional Formatting. That would give you three potential conditions I believe.

Otherwise you could use a simple bit of VB.

[cerbie1996]

I theory i should not need morethan 5 colours

[TheGroover]

As stated, for more than 3 conditions you will need a macro. As a starter for 10, put something like this in the sheet object you are working on. It will fire every time the worksheet changes, i.e. you enter a number and press enter.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim intCol As Integer
Dim intRow As Integer

intCol = 1 'Column you want to start in
intRow = 1 'Row you want to start in

For intCol = 1 To 10 ' 10 is column you want to finish in
For intRow = 1 To 10 '10 is the row you want to end in
'this will loop round from row 1 col 1 to row 10 col 10
'and colour the cell depending on whats in it
ActiveSheet.Cells(intRow, intCol).Select
Select Case ActiveCell.Text
Case 1 'cell has 1 in it
Selection.Interior.ColorIndex = 3 ' Red
Case 2 'cell has 2 in it
Selection.Interior.ColorIndex = 4 ' Green
Case 3 'cell has 3 in it
Selection.Interior.ColorIndex = 5 ' Blue
Case 4 'cell has 4 in it
Selection.Interior.ColorIndex = 6 ' Yellow
Case 5 'cell has 5 in it
Selection.Interior.ColorIndex = 7 ' Pink
Case Else ' everything else
'Do nothing
End Select
Next intRow
Next intCol
End Sub