Cell color change based on values

[kashifjillani]

Hi there,

I wonder if someone know how to change the background cell color based on value ranges(s)

I tried conditional formatting but it works between two values only, in my assignment I want to show:

River levels in relation to flood class
>=2m <2.6m is minor flood (background turns green)
>=2.6 <3 m is moderate flood (background turns yellow)
>=3m major flood (background turns red)

I hope it is possible in Office 2007

Cheerio

 

[Michael M]

Hi
What is the range of cells this will apply to ??
AND
What happens when River level is <2m ??

 

[kashifjillani]

Hi
What is the range of cells this will apply to ??
AND
What happens when River level is <2m ??

This classification would apply to whole row say BV7 to GK7 (2m, 2.6m, 3m)

Similarly other rivers with different classification BV8 to BV8 (3m, 3.4m, only minor and moderate flood nod classification for major)

To your second question, below 2m level the cell will stay sky blue colored.

Thanks mate for your prompt attempt, wish to hear from you today again.

Cheerio.

 

[Michael M]

Try this.....but the colours may be slightly different

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
    If Not Intersect(Target, Range("BV7:GK8")) Is Nothing Then
        Select Case Target
                Case Is < 2
                        icolor = 8
                Case Is < 2.6
                        icolor = 4
                Case Is < 3
                        icolor = 6
                Case Is > 3
                        icolor = 3
        End Select
        Target.Interior.ColorIndex = icolor
    End If
End Sub

 

[kashifjillani]

Try this.....but the colours may be slightly different

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
    If Not Intersect(Target, Range("BV7:GK8")) Is Nothing Then
        Select Case Target
                Case Is < 2
                        icolor = 8
                Case Is < 2.6
                        icolor = 4
                Case Is < 3
                        icolor = 6
                Case Is > 3
                        icolor = 3
        End Select
        Target.Interior.ColorIndex = icolor
    End If
End Sub

+
+
+

Thanks Michael,

It works,

Just one thing more, I want to apply it to other rows as well

e.g. BV8:GV8

BV9:GV9 and so on (the classification would keep changing with each row)

I tried to copy and paste under your code, but it didnt work.

Can you suggest on it please,

Thanks again,

 

[Coryj5]

Hi there,

I wonder if someone know how to change the background cell color based on value ranges(s)

I tried conditional formatting but it works between two values only, in my assignment I want to show:

River levels in relation to flood class
>=2m <2.6m is minor flood (background turns green)
>=2.6 <3 m is moderate flood (background turns yellow)
>=3m major flood (background turns red)

I hope it is possible in Office 2007

Cheerio

You can still use conditional formatting for this. in 2007, follow the below.
1. Highlight the block of cells you would like to conditional format.
2. Select conditional formatting and "manage rules"
3. Select "New Rule"
4. Use "format only cells that contain" and the drop down to "between", then write the first condition. ("between" 2m and 2.59999),
5. Follow #3 through #5 again until all conditions are met. the last one, instead of being between would be "greater than or equal to" 3m.
You should also have a condition to "less than" 2m, which you stated in the previous reply was sky blue. You can select the colors more easily this way.

Hope this helps.

 

[tablecloth]

Thanks for all the information provided..this is what I need too.

 

[kashifjillani]

You can still use conditional formatting for this. in 2007, follow the below.
1. Highlight the block of cells you would like to conditional format.
2. Select conditional formatting and "manage rules"
3. Select "New Rule"
4. Use "format only cells that contain" and the drop down to "between", then write the first condition. ("between" 2m and 2.59999),
5. Follow #3 through #5 again until all conditions are met. the last one, instead of being between would be "greater than or equal to" 3m.
You should also have a condition to "less than" 2m, which you stated in the previous reply was sky blue. You can select the colors more easily this way.

Hope this helps.

Thanks Cory,

I didnt know I would find solution (two instead of one) for this problem,

Thanks again to Michael and You,

Regards,

 

[Michael M]

There is always more than one way to solve the problem....as long as you have a solution !!

 

[Coryj5]

anytime, friend.