Vba question regarding conditional formatting

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:02 PM GMT

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

©2024 eLuminary LLC. All rights reserved.