Printable View
Hello I have code in the workbook attached that colours the rows based on the Call type Service Request or Incident (Column J) including the variations within them Incident-1, Incident-4 for example based on the urgency of the call and also time elasped into the SLA (column Z). So some rows will be red, some Amber and some Green.
I have written the code in two modules for Incidents and Service Requests and while it works OK I do wonder if it can be simplified and made quicker.
Shortened, but may not be quicker. Add all your conditions into an array and loop through them.
Code:Private Sub FormatIncidentRows()
Dim arr
Dim x As String, y As String, z As Long
Dim Tests As Long
Tests = 9
arr = Array( _
"""Incident-1""", """<50%""", 255, _
"""Incident-1""", """<100%""", 255, _
"""Incident-2""", """<50%""", 49407, _
"""Incident-2""", """<100%""", 49407)
Sheets("Raw Data").Cells.FormatConditions.Delete
Application.ScreenUpdating = False
With Columns("A:Z")
For i = 0 To Tests Step 3
x = arr(i): y = arr(i + 1): z = arr(i + 2)
.FormatConditions.Add Type:=xlExpression, Formula1:="=AND($J1=" & x & ",$Z1>=" & y & ")"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = z
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
Next i
End With
Application.ScreenUpdating = True
End Sub
I've managed to lose about 15 of the CFs by having the first CF colouring red any row that contains 100 in column Z and stopping if TRUE, then deleting all the others which referred to 100 in column Z.
In the attached I have constructed a table of all combinations:
Attachment 16224where:
- there's a letter r/a/g if you've got an explicit CF for a particular combination,
- filled with the colour that actually shows with your CFs (with one exception),
- a question mark just wondering if that combination should be green?
The exception is where I've put r shows a which means you may have a CF intended to show red there but it in fact shows amber.
The first thing I noticed is that the first 2 colmns were all red regardless of what was in column J.(Implemented)
Where there are similar r/a/g patterns in rows, theoretically you should be able to combine them (Not implemented):
ServiceRequest-1.1,ServiceRequest-1.2,ServiceRequest-2.1
ServiceRequest-3.1,ServiceRequest-3.2, and possibly also with ServiceRequest-3.3.
To help test I added rows to Raw Data from row 296 down of all combinations in the table, with the original colour in words in column I.
In VBA there's a new Module 4 which is a copy of your Module 1 with the very last CF in the module adjusted for the 100s and commented out some 15, now unnecessary, CFs.
p45Cal, Many thanks for your help and assistance. You've once again been a great help. Both the ? should be Amber and the 'r shows a' I've eliminated by copying the code for SR3.2 which took another CF out of the way.
I was looking into a conditional format formula using If, AND, OR but I don't believe that is possible. I'm my own biggest enemy at times as I think my code would improve if I planned out the logic required for it first. But I tend to take it a step at a time in an order that I believe is logical.
mdmackillop, Thanks very much for your help to. I've gone with p45cal's solution as I understand how it works should it need tweaking in the future.
Quick question though regarding the code if you don't mind answering. I'm a bit confused by the sections below ralating to z, how does that work to determine the colour, or have i completely misunderstood it.
x = arr(i): y = arr(i + 1): z = arr(i + 2) .Color = z
Folks,
Some real sincere thanks to all the contributors on this forum who have shared their knowledge to help others. Some recent knowledge I have picked up reading other posts and answers to my queries have for example reduced the run time on one of my jobs from 5 minutes to just 50 seconds. Frankly I'm astounded and can't wait to implement what I have learned from you all on my other procedures when I'm back into work on Monday.
Simple things like not copying and pasting but transferring data. But there seemed to be quite a difference when deleting or inserting columns by using .EntireColumn.Delete rather than .Delete Shift:=xlToLeft and when selecting a sheets data using .UsedRange rather than Selection.CurrentRegion.Select / Selection.Copy .
So thank you all most sincerely for all your help.
Your code for the CF takes three variables, $J1, $Z1 and Colour, I've simply arranged a sample of these in an array, laid out as a table by using line breaks. Each "row" shows the two conditions and the resultant colour. The code loops through these in steps of 3 to insert them into the CF code, avoiding all the repeated lines.
Another thing you could try is setting the whole grid to green fill, then you should be able to remove any cf making a cell green. (This sort of technique was needed when only 3 CFs were allowed per cell.)