Conditional Formatting

[Owen Kenny]

I am trying to colour individual cells in a large section of a worksheet,20 columns by 600 rows so that there are up to 10 different colours dependent on the value in each cell.
As I work on the sheet the values in the cells change and I need the colours to change as I go. Conditional formatting works just fine but there are not enough values available (3 choices only). Is it possible to add more choices. I am very limited in VB ability.
Yours hopefully Owen Kenny

[Tom Urtis]

Yes it is possible, a few questions:

(1) What is your range of cells?
(2) What is your range of values for each color? Example, 0 - 50 would be yellow, 51 - 100 would be green, etc.
(3) Are the values being added manually or are they calculated as the result of formulas in those cells?

Answer these questions and we can assist more efficiently.

[robfo0]

also, try doing a search on this board for conditional formatting, i know its been answered before

[Owen Kenny]

Currently I am using this macro but it does not update until I run the macro and I am using it repearedly and the time wasted is terrible.
This contains the ranges and the colours I am currently using.
The values change as a result of formulas in the cells.
Thank you very much for your help.
OK
Sub Rainbowchange()
For x = 10 To 570
For y = 57 To 68
With Worksheets("Sheet1").Cells(x, y)
Select Case .Value

Case Is > 2
.Interior.Color = RGB(11, 165, 11)

Case Is > 1.6
.Interior.Color = RGB(14, 214, 14)
Case Is > 1.2
.Interior.Color = RGB(58, 242, 58)
Case Is > 0.8
.Interior.Color = RGB(129, 247, 129)
Case Is > 0.4
.Interior.Color = RGB(199, 251, 199)
Case Is > 0.2
.Interior.Color = RGB(255, 255, 149)

Case Is > 0
.Interior.Color = RGB(255, 255, 195)
Case Is = 0
.Interior.Color = RGB(255, 255, 255)
Case Is > -0.2
.Interior.Color = RGB(252, 224, 224)
Case Is > -0.4
.Interior.Color = RGB(249, 189, 189)
Case Is > -0.8
.Interior.Color = RGB(245, 143, 143)
Case Is > -1.2
.Interior.Color = RGB(241, 101, 101)
Case Is > -1.6
.Interior.Color = RGB(238, 70, 70)
Case Is > -2
.Interior.Color = RGB(235, 27, 27)
Case Is < -1.99
.Interior.Color = RGB(254, 18, 18)
End Select
End With
Next y
Next x
End Sub

[Tom Schreiner]

I'm assuming the values in these cells are changed via links to other cells?
The easiest way to accomplish what you are trying to do might be to have direct control over the assigning of values to these cells.

Create a procedure with your select case statements, but no looping, and pass the value to this procedure. Pass the value of the cell and the address of the cell to be formatted. In other words, format the cells in sheet1 from the change event in sheet2.

Do you understand?

You also could pass place your code, as is, in the worksheet calculate event and run your code everytime a cells value is changed
via calculation.

Try it

Tom

[robfo0]

I found this code from a quick search of this board, guess whos code it was, the notorious TsTom It is a previous response to your exact question and uses the method he describes above

i changed the rows and columns to what you wanted them to be.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row > 9 And Target.Row < 570 And Target.Column > 56 And Target.Column < 69 Then
With Target
Select Case .Value

Case Is > 2
.Interior.Color = RGB(11, 165, 11)
Case Is > 1.6
.Interior.Color = RGB(14, 214, 14)
Case Is > 1.2
.Interior.Color = RGB(58, 242, 58)
Case Is > 0.8
.Interior.Color = RGB(129, 247, 129)
Case Is > 0.4
.Interior.Color = RGB(199, 251, 199)
Case Is > 0.2
.Interior.Color = RGB(255, 255, 149)

Case Is > 0
.Interior.Color = RGB(255, 255, 195)
Case Is = 0
.Interior.Color = RGB(255, 255, 255)
Case Is > -0.2
.Interior.Color = RGB(252, 224, 224)
Case Is > -0.4
.Interior.Color = RGB(249, 189, 189)
Case Is > -0.8
.Interior.Color = RGB(245, 143, 143)
Case Is > -1.2
.Interior.Color = RGB(241, 101, 101)
Case Is > -1.6
.Interior.Color = RGB(238, 70, 70)
Case Is > -2
.Interior.Color = RGB(235, 27, 27)
Case Is < -1.99
.Interior.Color = RGB(254, 18, 18)
End Select
End With
End If

End Sub

[Owen Kenny]

Thanks very much for the ideas . I'll give them a go and see if I can understand what's going on .

[Owen Kenny]

I'm afraid I can't follow this . My VB is not good and I had help with the original macro. I have modified it but am not able to develop my own. I'll try to clarify what I am doing.At the moment each worksheet is a separate entity and the cells within the range specified are deriving their value from cells outside the range but on the same worksheet.
Each column within the range has a formula in it and all of the cells in the column have the same formula but different values depending on their data sources. At the moment I am changing the formulas constantly trying to develop the magic formula that does exactly what I want. Each time I alter a formula the values in all cells within the column change and so their colours need to change .The macro I was using works well but is incredibly slow. Can I use the modifications suggested using Target to create a macro that will work much more quickly than the one I have been using.Looks like I'll have to do a lot more work on VB.I've looked at the other conditional formatting topics but I can't find one doing what I am after. If there is one and I'm missing it could you please let me know which page or date it is on and under what search topic.
Thanks very much for the efforts.
OK

[Tom Schreiner]

Hey Ken
If you'd like, send me a copy of your sheet.
TsTom@hotmail.com
Tom