Conditionnal formating for more than 3 colors [Closed]

Report
Posts
6
Registration date
Monday February 4, 2013
Status
Member
Last seen
February 7, 2013
-
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Hi,


I need some help with conditionnal formating. I have 27 conditions and for each I need a separate color.

1,0-2,0
2,0-4,0
4,0-6,0
6,0-8,0
8,0-10
10-20
20-40
40-60
60-80
80-100
100-200
200-400
400-600
600-800
800-1.000
1.000-2.000
2.000-4.000
4.000-6.000
6.000-8.000
8.000-10.000
10.000-20.000
20.000-40.000
40.000-60.000
60.000-80.000
80.000-100.000
>100.000

I found some information about "fixed" conditions (like terms or specific numbers), but not for condition which include between x and y.
This is what I get from excel in VBA, is there a "easy" way to combine this kind of formulas to get up to 27 conditions?



Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="0", Formula2:="1"
Selection.FormatConditions(1).Interior.ColorIndex = 53

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="1", Formula2:="2"
Selection.FormatConditions(2).Interior.ColorIndex = 52

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="2", Formula2:="4"
Selection.FormatConditions(3).Interior.ColorIndex = 51

Thanks a lot!

5 replies

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
Posts
6
Registration date
Monday February 4, 2013
Status
Member
Last seen
February 7, 2013

Hi,
Thanks, but it still doesn't answer my question.
None of these answers describes the if condition, I tried:
'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:A10")

If Not Intersect(Target, WatchRange) Is Nothing Then
Select Case CellVal
Case 0 To 10
Target.Interior.ColorIndex = 5
Case 10 To 20
Target.Interior.ColorIndex = 10
Case 21 To 30
Target.Interior.ColorIndex = 6
Case 31 To 40
Target.Interior.ColorIndex = 46
Case 41 To 50
Target.Interior.ColorIndex = 45
End Select
End If
End Sub
'END OF CODE

but this only works, if you fill the cells after having programmed the code. I already have an excel file with a lot of numbers and I need them to change automatically into a color code, if this is possible. I didn't manage to do copy/paste, well I can do it one by one, not a whole colomn!

Any ideas?
Thanks!
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
Since you already have numbers and you want to change those, one way would doing some thing like this
public Sub applyColor()
Dim WatchRange      As Range
Dim Target          As Range
Dim cellValue       As Integer


    Set WatchRange = Union(Range("A1:A10"), Range("B5:b7")) 'apply format to these range
    
    For Each Target In WatchRange
        If Target <> "" And IsNumeric(Target) Then
            cellValue = Target
            Select Case cellValue
                Case 0 To 10
                    Target.Interior.ColorIndex = 5
                Case 10 To 20
                    Target.Interior.ColorIndex = 10
                Case 21 To 30
                    Target.Interior.ColorIndex = 6
                Case 31 To 40
                  Target.Interior.ColorIndex = 46
                Case 41 To 50
                    Target.Interior.ColorIndex = 45
            End Select
        End If
    Next
End Sub
Posts
6
Registration date
Monday February 4, 2013
Status
Member
Last seen
February 7, 2013

HI,


thanks a lot..
this is how it works for me.. do you see anything what could cause problems?

'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:A500")

For Each Target In WatchRange
If Target <> "" And IsNumeric(Target) Then
cellValue = Target
Select Case cellValue
Case 0 To 10
Target.Interior.ColorIndex = 5
Case 10 To 20
Target.Interior.ColorIndex = 10
Case 21 To 30
Target.Interior.ColorIndex = 6
Case 31 To 40
Target.Interior.ColorIndex = 46
Case 41 To 50
Target.Interior.ColorIndex = 45
End Select
End If
Next
End Sub
'END OF CODE
Posts
6
Registration date
Monday February 4, 2013
Status
Member
Last seen
February 7, 2013

Hi,

thank you so much, it really works quite good now. But I still have 3 more questions just to optimize my code:

'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Integer
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:BZ1000")


For Each Target In WatchRange
If Target <> "" And IsNumeric(Target) Then
cellValue = Target
Select Case cellValue
Case 0 To 1
Target.Interior.ColorIndex = 53
Case 1 To 2
Target.Interior.ColorIndex = 52
Case 2 To 4
Target.Interior.ColorIndex = 51
Case 4 To 6
Target.Interior.ColorIndex = 49
Case 6 To 8
Target.Interior.ColorIndex = 11
Case 8 To 10
Target.Interior.ColorIndex = 55
Case 10 To 20
Target.Interior.ColorIndex = 56
Case 20 To 40
Target.Interior.ColorIndex = 9
Case 40 To 60
Target.Interior.ColorIndex = 46
Case 60 To 80
Target.Interior.ColorIndex = 12
Case 80 To 100
Target.Interior.ColorIndex = 10
Case 100 To 200
Target.Interior.ColorIndex = 14
Case 200 To 400
Target.Interior.ColorIndex = 5
Case 400 To 600
Target.Interior.ColorIndex = 47
Case 600 To 800
Target.Interior.ColorIndex = 16
Case 800 To 1000
Target.Interior.ColorIndex = 3
Case 1000 To 2000
Target.Interior.ColorIndex = 45
Case 2000 To 4000
Target.Interior.ColorIndex = 43
Case 4000 To 6000
Target.Interior.ColorIndex = 50
Case 6000 To 8000
Target.Interior.ColorIndex = 42
Case 8000 To 10000
Target.Interior.ColorIndex = 41
Case 10000 To 20000
Target.Interior.ColorIndex = 13
Case 20000 To 40000
Target.Interior.ColorIndex = 48
Case 40000 To 60000
Target.Interior.ColorIndex = 7
Case 60000 To 80000
Target.Interior.ColorIndex = 44
Case 80000 To 100000
Target.Interior.ColorIndex = 6
Case 100000 To 100000000
Target.Interior.ColorIndex = 4

End Select
End If
Next
End Sub
'END OF CODE


Here my questions:
1. from the number 32800 it tells me that there is an error message: error 6, overflow - is there anything I can do to fix that? when I'm putting in a number lower and press enter, then the color will still appear in the cells with a higher amount. (>32800)
2. I'd like to attribute the code to the whole datasheet - is this too much for the program? I tried to put in Range ("A1:ZZ1000") and it doesn't work.. is it possible, or do you advice me to go for the range ("A1:BZ1000")?
3. For the last case i'd like to say case >100000, just write more than 100000?

Thanks so much for your help!!!
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
1. from the number 32800 it tells me that there is an error message: error 6, overflow - is there anything I can do to fix that? when I'm putting in a number lower and press enter, then the color will still appear in the cells with a higher amount. (>32800)
Dim CellVal As Integer is declared as Integer. You are exceeding the interger limit. Try
Dim CellVal As Long

2. I'd like to attribute the code to the whole datasheet - is this too much for the program? I tried to put in Range ("A1:ZZ1000") and it doesn't work.. is it possible, or do you advice me to go for the range ("A1:BZ1000")?
Your code at https://ccm.net/forum/affich-686888-conditionnal-formating-for-more-than-3-colors#5 is not making much sense to me

'This is saying of the selected range where the change is made is more than one cell then exit
if Target.Cells.Count > 1 Then Exit Sub

'Why would you want to loop thru all cells in ' WatchRange when all you are doing is changing color on one cell that is defined by Target
For Each Target In WatchRange

3. For the last case i'd like to say case >100000, just write more than 100000?
Case Is > 100000
Target.Interior.ColorIndex = 4
Posts
6
Registration date
Monday February 4, 2013
Status
Member
Last seen
February 7, 2013

Hi,

thanks again for your answer!
As you might figure, I'm just copying and pasting codes together and look if it's working, so this is may be why it doesn't make any sense to you. Somehow it's working, now without error message. Anyway, I still don't get how I can change the watch range.
This is what I have now, but it's still limited to A1 to BZ1000..

'START OF CODE
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim WatchRange As Range
Dim CellVal As Long
If Target = "" Or Not IsNumeric(Target) Then Exit Sub
CellVal = Target
Set WatchRange = Range("A1:BZ1000")


For Each Target In WatchRange
If Target <> "" And IsNumeric(Target) Then
cellValue = Target
Select Case cellValue
Case 0 To 1
Target.Interior.ColorIndex = 53
Case 1 To 2
Target.Interior.ColorIndex = 52
Case 2 To 4
Target.Interior.ColorIndex = 51
Case 4 To 6
Target.Interior.ColorIndex = 49
Case 6 To 8
Target.Interior.ColorIndex = 11
Case 8 To 10
Target.Interior.ColorIndex = 55
Case 10 To 20
Target.Interior.ColorIndex = 56
Case 20 To 40
Target.Interior.ColorIndex = 9
Case 40 To 60
Target.Interior.ColorIndex = 46
Case 60 To 80
Target.Interior.ColorIndex = 12
Case 80 To 100
Target.Interior.ColorIndex = 10
Case 100 To 200
Target.Interior.ColorIndex = 14
Case 200 To 400
Target.Interior.ColorIndex = 5
Case 400 To 600
Target.Interior.ColorIndex = 47
Case 600 To 800
Target.Interior.ColorIndex = 16
Case 800 To 1000
Target.Interior.ColorIndex = 3
Case 1000 To 2000
Target.Interior.ColorIndex = 45
Case 2000 To 4000
Target.Interior.ColorIndex = 43
Case 4000 To 6000
Target.Interior.ColorIndex = 50
Case 6000 To 8000
Target.Interior.ColorIndex = 42
Case 8000 To 10000
Target.Interior.ColorIndex = 41
Case 10000 To 20000
Target.Interior.ColorIndex = 13
Case 20000 To 40000
Target.Interior.ColorIndex = 48
Case 40000 To 60000
Target.Interior.ColorIndex = 7
Case 60000 To 80000
Target.Interior.ColorIndex = 44
Case 80000 To 100000
Target.Interior.ColorIndex = 6
Case Is > 100000
Target.Interior.ColorIndex = 4

End Select
End If
Next
End Sub
'END OF CODE
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
Have you given some thoughts to this

2. I'd like to attribute the code to the whole datasheet - is this too much for the program? I tried to put in Range ("A1:ZZ1000") and it doesn't work.. is it possible, or do you advice me to go for the range ("A1:BZ1000")?  
Your code at https://ccm.net/forum/affich-686888-conditionnal-formating-for-more-than-3-colors#5 is not making much sense to me  

'This is saying of the selected range where the change is made is more than one cell then exit  
if Target.Cells.Count > 1 Then Exit Sub  

'Why would you want to loop thru all cells in ' WatchRange when all you are doing is changing color on one cell that is defined by Target  
For Each Target In WatchRange 
soeck
Posts
6
Registration date
Monday February 4, 2013
Status
Member
Last seen
February 7, 2013

I tried, sorry, but I don't understand what you mean.
if i'm deleting this message 'if Target.Cells.Count > 1 Then Exit Sub', than all i can do is to type values in cells that they change color.
i looked up in many forums, but none of them is answering my question how to apply the code for the whole worksheet. but as it's working for the range a1:bz1000, i'll go for it.
thanks again.
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
756
read the comments below

'This is an "event" routine. this is fired on its own when on the sheet some data is
'is changed. parameter "Target" that is paased to it is the range of cells thats changed
'and which caused the event ti fire. "Target" could be one cell or more than one cell
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim WatchRange As Range
Dim CellVal As Long
    
    'says if "Target" is blank or is not number, get out of routine
    'this will not work if event is fired for for than one cell
    'this call is good for a single cell
    If Target = "" Or Not IsNumeric(Target) Then Exit Sub
    
    'storing "Target" cell value. Again will not work if "Target" cpnsist of
    'more  than one cell
    CellVal = Target
    
    'setting a range
    Set WatchRange = Range("A1:BZ1000")
    
    'two issues here. one use of "Target" here. This target has nothing to
    'do with "Target" that was passed to routine and contained cells whose value
    'have changed. All this line is doing is looping through cells defined in range
    '"WatchRange", one cell at a time.
    For Each Target In WatchRange
    
        'this will work
        If Target <> "" And IsNumeric(Target) Then



So first thing you need to understand is what is event is about and does it fit your requirement. Earlier you mentioned that your sheet already has numbers and now you want to apply the colors. Well since sheets already has numbers, this event will not fire for them till you edit them (again see the event comments). In that case you have to write a similar routine, that will not be an event but a regular routine that will just loop through the cells that you want to apply colors to (that's what i earlier posted for you at https://ccm.net/forum/affich-686888-conditionnal-formating-for-more-than-3-colors#3 . So it all boils down to what you actually want to achieve