Multi criteria conditional formatting

Solved/Closed
Report
Posts
3
Registration date
Wednesday May 18, 2011
Status
Member
Last seen
May 25, 2011
-
Posts
3
Registration date
Wednesday May 18, 2011
Status
Member
Last seen
May 25, 2011
-
Hello,

I am trying to work out how to reference to cells for an if statement using conditonally formatting (through VBA), I have come up with the following code which works, but under the If 'Processing' I also want to check another cell to determine what colour the row should be:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyPlage As Range
Set MyPlage = Range("K5:N2565")
For Each Cell In MyPlage

If UCase(Cell.Value) = "PROCESSING" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 26
ElseIf UCase(Cell.Value) = "HELD IN ABEYANCE" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 36
ElseIf UCase(Cell.Value) = "OFFER" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 0
ElseIf UCase(Cell.Value) = "AUTHORITY" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 50
ElseIf UCase(Cell.Value) = "APPROVED" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 50
ElseIf UCase(Cell.Value) = "WITHDRAWN" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 50
ElseIf UCase(Cell.Value) = "NOT APPROVED" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 50
ElseIf UCase(Cell.Value) = "NFA" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 50
ElseIf UCase(Cell.Value) = "CANCELLATION" Then
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 50
Else
Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = 0
End If

Next
End Sub


Basically the cell processing is under colum N, but then I want to check it against colum K.

Any help would be great, sorry if it is confusing, I can see what I want in my head just cant get it to work in vba




3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
First try to avoid so many IF statements. You can use select

Some thing like this would be better

Private Sub Worksheet_Change(ByVal Target As Range)
   
   Dim MyPlage                As Range
   Dim iColorIndex            As Integer
   
   Set MyPlage = Range("K5:N2565")
   
      For Each Cell In MyPlage
         Select Case UCase(Cell.Value)
            Case Is = "PROCESSING"
               iColorIndex = 26
            Case Is = "HELD IN ABEYANCE"
               iColorIndex = 36
            Case Is = "AUTHORITY", "APPROVED", "WITHDRAWN", "NOT APPROVED", "NFA", "CANCELLATION"
               iColorIndex = 50
            Case Else
               iColorIndex = 0
         End Select
         Range("A" + CStr(Cell.Row) + ":N" + CStr(Cell.Row)).Interior.ColorIndex = iColorIndex
   Next
End Sub


Now understand what the code is doing. Basically if there is any change in value in the worksheet (be it a cell or a range of cells), this code is would get executed. What is changed is in the variable "Target"

The code is set to scan each cell in the range K5:N2565 and does color to a range of cells. So you are not right that it looks only in column K. It does look at column K but also L, M and N and all between rows 5 and 2565. Hope that make things more clear for you.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
3
Registration date
Wednesday May 18, 2011
Status
Member
Last seen
May 25, 2011

Thank you rizvisa1, that has certainely thined the code down substantially, I am still confused though, and I am sorry being new to VBA coding, what I am trying to do is probably really simple yet I am struggling.

Under the case = processing statement, if this is true how do I then check against the cell in column k for the same row to determine, if it meets a criteria with in the cell? I.e.

If cell (column N) = processing then
If cell (column K) = 096 then
Row would be blue
Elseif cell (column K) = 003 then
row would be orange
else
row would be pink
End If
End If

Sorry I have used if statements, (not in the code though) but purely to show what I am trying to do.

Any and all help is greatly appreciated
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I think some thing like this should work

Private Sub Worksheet_Change(ByVal Target As Range)
    
   Dim rngCells               As Range
   Dim rngRow                 As Range
   Dim vValueN                As Variant
   Dim vValueK                As Variant
   Dim lOrange                As Long
   Dim lPink                  As Long
   Dim lBlue                  As Long
   Dim lColor                 As Long
   Dim lWhite                 As Long
   Dim lRow                   As Long
   
   lPink = &HFF00FF
   lBlue = &HFF0000
   lOrange = &H66FF
   lWhite = &HFFFFFF
   
   Set rngCells = Cells.Find(What:="*", After:=Cells(1, 1), LookAt:=xlByRows, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
   If (rngCells Is Nothing) Then
      Exit Sub
   ElseIf (rngCells.Row < 5) Then
      Exit Sub
   Else
      Set rngCells = Rows("5:" & rngCells.Row)
   End If
   
   For Each rngRow In rngCells.Rows
      lRow = rngRow.Row
      vValueN = UCase(Cells(lRow, "N"))
      vValueK = UCase(Cells(lRow, "K"))
      
      Select Case vValueN
         Case Is = "PROCESSING"
            Select Case vValueK
               Case Is = "096"
                  lColor = lBlue
               Case Is = "003"
                  lColor = lOrange
               Case Else
                  lColor = lPink
            End Select
               
         Case Is = "HELD IN ABEYANCE"
            
         Case Is = "AUTHORITY", "APPROVED", "WITHDRAWN", "NOT APPROVED", "NFA", "CANCELLATION"
           
         Case Else
            
      End Select
      Range("A" & lRow & ":N" & lRow).Interior.Color = lColor
   Next
End Sub
Posts
3
Registration date
Wednesday May 18, 2011
Status
Member
Last seen
May 25, 2011

Thank you rizvisa1 for all your help with this coding, it has solved everything.