If, If, If

G

G0DD3Rs

New Member
Joined
Oct 22, 2004
Messages
26
All, the code below says if anything in the range is Low, Medium or High (referring to risk) then colour the cell appropriately.

What I'd like to do is say if B1 says High then make the cell D2 red, or if B2 says medium make D2 amber or if B11 says Low then make D11 green.

Any ideas?


Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    '   Multiple Conditional Format
    Dim rng As Range
     '   Only look at single cell changes
    If Target.Count > 1 Then Exit Sub
    Set rng = Range("I3:I11")
    '   Only look at that range
    If Intersect(Target, rng) Is Nothing Then Exit Sub
        If Target <> "LOW" Or Target <> "MEDIUM" Or Target <> "HIGH" Then _
            Target.Interior.ColorIndex = 0
        If Target = "Low" Then Target.Interior.Color = vbGreen    'Green
        If Target = "Medium" Then Target.Interior.Color = vbBlue  'Blue
        If Target = "High" Then Target.Interior.Color = vbRed     'Red
End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I3:I11")) Is Nothing And _
Target.Count = 1 Then
Select Case UCase(Target.Value)
Case "LOW": Target.Interior.Color = vbGreen
Case "MEDIUM": Target.Interior.Color = vbBlue
Case "HIGH": Target.Interior.Color = vbRed
Case Else: Target.Interior.ColorIndex = 0
End Select
End If
End Sub
 
Upvote 0
You might be better off using conditional formatting instead of code

In your example you would put in D2 (etc) a conditional format of
=$B$2="LOW" using 'Formula Is' and then select the green

You would then add the other colours and copy the CF down to the end of your data

HTH (y)
 
Upvote 0
Maybe this'll get you close to what you want:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#007F00">'   Multiple Conditional Format</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
     <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("B1:B2, B11")
    <SPAN style="color:#007F00">'   Only look at that range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target <> "LOW" <SPAN style="color:#00007F">Or</SPAN> Target <> "MEDIUM" <SPAN style="color:#00007F">Or</SPAN> Target <> "HIGH" Then _
            Target.Interior.ColorIndex = 0
            <SPAN style="color:#00007F">If</SPAN> Target = [B1] And Target = "High" <SPAN style="color:#00007F">Then</SPAN> [D2].Interior.ColorIndex = 3           <SPAN style="color:#007F00">'   Red</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target = [B2] And Target = "Medium" <SPAN style="color:#00007F">Then</SPAN> [D2].Interior.ColorIndex = 44     <SPAN style="color:#007F00">'   Amber</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target = [B11] And Target = "Low" <SPAN style="color:#00007F">Then</SPAN> [D11].Interior.ColorIndex = 4         <SPAN style="color:#007F00">'    Green</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
 
Upvote 0
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("b1:b11")) Is Nothing And _
Target.Count = 1 Then
Select Case UCase(Target.Value)
Case "LOW": Target.Offset(0, 2).Interior.Color = vbGreen
Case "MEDIUM": Target.Offset(0, 2).Interior.Color = vbBlue
Case "HIGH": Target.Offset(0, 2).Interior.Color = vbRed
Case Else: Target.Offset(0, 2).Interior.ColorIndex = 0
End Select
End If
End Sub
 
Upvote 0
pennysaver said:
Maybe this'll get you close to what you want:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#007F00">'   Multiple Conditional Format</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range
     <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> rng = Range("B1:B2, B11")
    <SPAN style="color:#007F00">'   Only look at that range</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">If</SPAN> Target <> "LOW" <SPAN style="color:#00007F">Or</SPAN> Target <> "MEDIUM" <SPAN style="color:#00007F">Or</SPAN> Target <> "HIGH" Then _
            Target.Interior.ColorIndex = 0
            <SPAN style="color:#00007F">If</SPAN> Target = [B1] And Target = "High" <SPAN style="color:#00007F">Then</SPAN> [D2].Interior.ColorIndex = 3           <SPAN style="color:#007F00">'   Red</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target = [B2] And Target = "Medium" <SPAN style="color:#00007F">Then</SPAN> [D2].Interior.ColorIndex = 44     <SPAN style="color:#007F00">'   Amber</SPAN>
            <SPAN style="color:#00007F">If</SPAN> Target = [B11] And Target = "Low" <SPAN style="color:#00007F">Then</SPAN> [D11].Interior.ColorIndex = 4         <SPAN style="color:#007F00">'    Green</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

Smitty
Click to expand...

This looks vaguely what I'm after but it's too specific. It needs to look at whatever is in all cells between B3:B11 and set the corresponding I cell to be either red, amber or green.
 
Upvote 0
Si,
With only 3 conditions to be met then use Conditional Formatting, it will do the job admirably.

Select Cells I3 to I11
Select Format > Conditional Formatting
Choose 'Formula Is'
Enter =B3="Low"
Choose Green
Repeat this by changing low to medium and high and the colour to amber and red

If your B3:B11 cell does not contain any of the words then the 'I' cell will remain the base colour, normally white.

If you weant a VBA solution then do make that clear please
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Help!!!
Replies
6
Views
231
Anthony47
A
R
  • Solved
change color of a cell, depending on value
Replies
5
Views
469
Georgiboy
Georgiboy
I
  • Solved
vba Code shows wrong format in cell & autosum also ignors the value
Replies
3
Views
264
RobP
R
I
  • Solved
VBA Color Changing Cell with multiple clicks
Replies
4
Views
701
Inquiring_Minds
I
I
  • Question
Data formatting for Chart
Replies
3
Views
324
IREALLYambatman
I

Forum statistics

Threads
1,214,982
Messages
6,122,575
Members
449,089
Latest member
Motoracer88

Share this page

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