Conditional Formatting VBA

[trevormay99]

I have a code that organizes raw data into multiple sheets, without getting into too much detail, it lists the task from the raw data with short descriptions, and completed date as well as scheduled date for each of the tasks, if the task was completed late (after the schedule date) I want it to be shaded red, if it was completed on time or early, then shaded green. However I am having trouble creating a code to apply this conditional formatting. I also need it to be applied to all sheets, is this possible or will it run too slow to be useful.

 

[Joe4]

What columns are these dates in? Is this true for all sheets in the workbook?
Might these columns contain any blanks, and if so, how would you like those to be handled?

In cases like this, it is often valuable to see a sample of your data so we can see how your data is structured, if there are headers/trailers/blank rows, etc.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[trevormay99]

I will try to get the add in to work, for now here is a snip, column F contains the date completed, column L contains the targeted scheduled date, so if F<=L (green shade) if F > L red shade

for areas with blanks shade yellow

 

[trevormay99]

Installed the add in here you go

Macro Fix V1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Scheduling Report
2	ELECT
3	Work Order	WO Description	WO Long Description	Process	Asset Priority	Schedule Date	Status	Type	Asset	Labor	Craft	Target Date
4	W23D11804	Carry-over Section - Equipment/Process Improvement - Electrical	Need Two Process button installed 1 on the drive side of the carry over section and 1 on the operator side of the carry over section they will both need conduit and boxes installed		7	4/6/23	RELEASED	EPI	M6744	40.00	Elect
5	W23D11803	Backing Film Applicator - Equipment/Process Improvement - Electrical	need a process stop install on an exiting electrical box to the north of the backing film station its number is 0410PS23		3	4/8/23	RELEASED	EPI	M6694	40.00	Elect
6	W23D15958	Limestone Pre Crusher Catwalks/Frame/Stairs - Corrective - Post-Repair - Electrical	Run Conduit for for heater cords on pre crusher See Modline Zack		1	4/22/23	RELEASED	COR	Q0021	1.00	Elect
7	W23D13469	IKO4WLS Dragon Tooth Pull Roll Lane #4 - 4 Month Life Cycle 1 - Mechanical	- Removal of Dragon Tooth Pull Rolls - Inspections to perform - Replacement of Dragon Tooth Pull Rolls - Speed Match	STOP	7	5/8/23	WSCHED	LIF	S4730	0.25	Elect	5/8/23
8	W23D13476	IKO4WLS Dragon Tooth Pull Roll Lane #1 - 4 Month Life Cycle 1 - Mechanical	- Removal of Dragon Tooth Pull Rolls - Inspections to Perform - Replacement of Dragon Tooth Pull Rolls - Speed Match	STOP	7	5/8/23	WSCHED	LIF	S4732	0.25	Elect	5/8/23
Pre-Meeting

 

[trevormay99]

Additionally, I have come across an issue in which this particular table generates twice, once at A3 and again at LH3, cannot figure out the error in the code

 

[Joe4]

Try this code:

Sub MyCFRules()

    Dim ws As Worksheet
    Dim lr As Long
    Dim rng As Range
    
'   Loop through all sheets
    For Each ws In ActiveWorkbook.Worksheets
'       Find last row with data in column A
        lr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
'       Set range to format
        Set rng = ws.Range("A3:L" & lr)
'       Set up first CF rule
        With rng
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$F3>$L3"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
            End With
        End With
'       Set up second CF rule
        With rng
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$F3<=$L3"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5296274
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = True
        End With
'       Set up third CF rule
        With rng
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$L3="""""
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            With .FormatConditions(1).Interior
                .PatternColorIndex = xlAutomatic
                .Color = 65535
                .TintAndShade = 0
            End With
            .FormatConditions(1).StopIfTrue = True
        End With
    Next ws
        
End Sub