Multi criteria conditional formatting
Solved/Closed
bretsh
Posts
3
Registration date
Wednesday May 18, 2011
Status
Member
Last seen
May 25, 2011
-
May 17, 2011 at 11:08 PM
bretsh Posts 3 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011 - May 25, 2011 at 10:54 PM
bretsh Posts 3 Registration date Wednesday May 18, 2011 Status Member Last seen May 25, 2011 - May 25, 2011 at 10:54 PM
Related:
- Multi criteria conditional formatting
- Conditional formatting if two cells match ✓ - Excel Forum
- Apply conditional formatting to the selected cells ✓ - Excel Forum
- If cell contains (multiple text criteria) then return (corresponding text criteria) ✓ - Excel Forum
- How to re/name a cell if another cell contains X ✓ - Excel Forum
- How to clear formatting in excel - Guide
3 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 24, 2011 at 09:56 AM
May 24, 2011 at 09:56 AM
First try to avoid so many IF statements. You can use select
Some thing like this would be better
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.
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.
bretsh
Posts
3
Registration date
Wednesday May 18, 2011
Status
Member
Last seen
May 25, 2011
May 24, 2011 at 08:23 PM
May 24, 2011 at 08:23 PM
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 25, 2011 at 07:24 AM
May 25, 2011 at 07:24 AM
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
bretsh
Posts
3
Registration date
Wednesday May 18, 2011
Status
Member
Last seen
May 25, 2011
May 25, 2011 at 10:54 PM
May 25, 2011 at 10:54 PM
Thank you rizvisa1 for all your help with this coding, it has solved everything.
