Conditional Formatting

Owen Kenny

New Member
Joined
Apr 3, 2002
Messages
4
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
also, try doing a search on this board for conditional formatting, i know its been answered before :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top