Vba question regarding conditional formatting
Contents
Question
QUESTION: Can you please help me with transforming the VBA script below. Right now the script is applying conditional formatting to the cells in a specific range. I'd like to transform it and make it apply the conditional formatting on another range - for example if Cell B1=10, then Cell A1 to become red, if cell B2=2, then Cell A2 to become yellow. Here's the script i'm using:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer Dim oCells As Range
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
aw z
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
ANSWER: Vladislav,
I have made the necessary adjustment in the code. You said if the target cell is 10, then the corresponding cell in column A would be red. However, for the Case 6 to 20 you color the cell a greenish color (icolor = 12). If you want it red, change the 12 to a 3 to color the cell Red.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer Dim oCells As Range
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
' offset to the left 1 cell to color column A
' in the same row as the target cell
Target.Offset(0,-1).Interior.ColorIndex = icolor
End If
End Sub
---------- FOLLOW-UP ----------
QUESTION: I can do this very simple with the integrated conditional formatting, but since i'm using2003, i'm limitted to only 3 conditions and i need several more. Is it possible at all with the script i've sent, or i should try to make something more complex?
Vladislav
Answer
Vladislav,
You can nest your conditions
You cited ranges A1:C10 and D1:F10
I have put an embedded case statement in the cases of the existing case statement. It is just representative and you can modify it to fit your needs. I believe this an approach to the situation you describe.
Also, the event fires if the trigger cell is in either of A1:C10 or D1:F10
just realized I had not finished constructing my IF statement because I was focused on doing the embedded Case statement. Here is the revised code with the IF statement set up properly
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer Dim oCells As Range Dim r1 As Range, r2 As Range
' don't do anything if multiple cells triggered the event If Target.count > 1 then exit sub
If Not Intersect(Target, Range("A1:C10")) Is Nothing Then
Set r1 = Target ' cell in A1:C10 range
Set r2 = Target.Offset(0, 3) ' corresponding cell in D1:F10 range
ElseIf Not Intersect(Target, Range("D1:F10")) Is Nothing Then
Set r1 = Target.Offset(0, -3) ' cell in A1:C10 range
Set r2 = Target ' corresponding cell in D1:F10 range
Else
' Target is not in either of the ranges, so exit event
Exit Sub
End If
Select Case r1
Case 1 To 5
Select Case r2
Case 0 To 10
icolor = 6
Case 11 To 20
icolor = 7
Case 21 To 30
icolor = 8
End Select
Case 6 To 10
Select Case r2
Case 0 To 10
icolor = 9
Case 11 To 20
icolor = 10
Case 21 To 30
icolor = 11
End Select
Case 11 To 15
Select Case r2
Case 0 To 10
icolor = 12
Case 11 To 20
icolor = 13
Case 21 To 30
icolor = 14
End Select
Case 16 To 20
Select Case r2
Case 0 To 10
icolor = 15
Case 11 To 20
icolor = 16
Case 21 To 30
icolor = 17
End Select
Case 21 To 25
Select Case r2
Case 0 To 10
icolor = 18
Case 11 To 20
icolor = 19
Case 21 To 30
icolor = 20
End Select
Case 26 To 30
Select Case r2
Case 0 To 10
icolor = 21
Case 11 To 20
icolor = 22
Case 21 To 30
icolor = 23
End Select
Case Else
'Whatever
End Select
' set color in the first range
r1.Interior.ColorIndex = icolor
End Sub
Question
Can you please help me with transforming the VBA script below. Right now the script is applying conditional formatting to the cells in a specific range. I'd like to transform it and make it apply the conditional formatting on another range - for example if Cell B1=10, then Cell A1 to become red, if cell B2=2, then Cell A2 to become yellow. Here's the script i'm using:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer Dim oCells As Range
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then aw z
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
Answer
Vladislav
It seems like you have written your own program to conditionally change the interior color of a the target cell. You could possibly have done this with Excel's built in conditional formatting if you are using2007. Earlier versions were limited to 3 conditions, so maybe that is why you wrote your own. In any case, if you change a value in column B and you want the format in column A of the same row to change, subject to your Select Case options, I believe all you will need to do is change the last line to
Target.Offset(0,-1).ColorIndex=icolor
The following worked for me:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer Dim oCells As Range
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then Exit Sub
Select Case Target.Value
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
End Select
Target.Offset(0, -1).Interior.ColorIndex = icolor
End Sub
Advertisement