VBA Conditional Formatting

[bino1121]

Currently confused about VBA conditional formatting I have not been able to find an example too similar to mine that I can edit just yet. But I have a report that ranges from A:J I am trying to set up if conditions for conditional formatting so that if F2 = G2 change to green J2 fills in with "NO ACTION", if G2<F2 J2 fills in with "NO action" change to green, if G2 > F2 change color to yellow or red J2 fills in with "action required". Then for loop around so that it evaluates all F and G to the last row and fills in the corresponding J with the correct message.

 

[rlv01]

I'm not clear which cells you want to change using CF, but I'll assume the Col J values for an example. Conditional formatting can handle the formatting end, e.g. "change to green" if F2=G2. But you will need a different mechanism, usually a formula to change the cell data, say to "NO ACTION"

Book9
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Data Col 1	Data Col 2	Data Col 3	Data Col 4	Data Col 5	Data Col 6	Data Col 7	Data Col 8	Data Col 9	Data Col 10	Data Col 11	Data Col 12	Data Col 13
2	nanninose	nurry	tread	albertina	bedewoman	240	240	vehicle	utraquist	NO ACTION	vagas	noninjury	vitta
3	chupon	rears	reshuffle	guahivo	molding	133	333	depsides	thairm	ACTION REQUIRED	firetower	ranarium	approach
4	boun	miriamne	misstops	liberal	aerostat	186	101	bammed	caymans	NO ACTION	oleg	dird	forloin
5	pinkroot	outlearns	everly	thegndom	idahoans	101	277	intersect	callants	ACTION REQUIRED	glottic	baguettes	dialonian
6	polynesia	endings	landrace	champacs	gyrodyne	274	165	glochids	tourneys	NO ACTION	reformats	nudest	marjorams
7	imaginate	letting	calzoons	resumes	wittawer	106	260	perfumer	leisters	ACTION REQUIRED	forefaces	emanating	remolds
8	vanaspati	bakongo	fingent	sarna	simeon	254	147	amorino	ornation	NO ACTION	arbutes	winterize	isostere
9	receded	sharpen	scraiched	salariego	tahkhana	283	286	pantiles	descend	ACTION REQUIRED	telurgy	raphael	simeon
10	resituate	topcastle	callants	codol	forefaces	111	234	writhled	guahivo	ACTION REQUIRED	sibness	skinking	evoke
Sheet1

Cell Formulas
Range	Formula
J2:J10	J2	=IF(G2<=F2,"NO ACTION",IF(G2>F2,"ACTION REQUIRED"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J10	Cell Value	beginning with "ACTION REQUIRED"	text	NO
J2:J10	Cell Value	contains "NO ACTION"	text	NO

 

[bino1121]

I'm not clear which cells you want to change using CF, but I'll assume the Col J values for an example. Conditional formatting can handle the formatting end, e.g. "change to green" if F2=G2. But you will need a different mechanism, usually a formula to change the cell data, say to "NO ACTION"

Book9
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Data Col 1	Data Col 2	Data Col 3	Data Col 4	Data Col 5	Data Col 6	Data Col 7	Data Col 8	Data Col 9	Data Col 10	Data Col 11	Data Col 12	Data Col 13
2	nanninose	nurry	tread	albertina	bedewoman	240	240	vehicle	utraquist	NO ACTION	vagas	noninjury	vitta
3	chupon	rears	reshuffle	guahivo	molding	133	333	depsides	thairm	ACTION REQUIRED	firetower	ranarium	approach
4	boun	miriamne	misstops	liberal	aerostat	186	101	bammed	caymans	NO ACTION	oleg	dird	forloin
5	pinkroot	outlearns	everly	thegndom	idahoans	101	277	intersect	callants	ACTION REQUIRED	glottic	baguettes	dialonian
6	polynesia	endings	landrace	champacs	gyrodyne	274	165	glochids	tourneys	NO ACTION	reformats	nudest	marjorams
7	imaginate	letting	calzoons	resumes	wittawer	106	260	perfumer	leisters	ACTION REQUIRED	forefaces	emanating	remolds
8	vanaspati	bakongo	fingent	sarna	simeon	254	147	amorino	ornation	NO ACTION	arbutes	winterize	isostere
9	receded	sharpen	scraiched	salariego	tahkhana	283	286	pantiles	descend	ACTION REQUIRED	telurgy	raphael	simeon
10	resituate	topcastle	callants	codol	forefaces	111	234	writhled	guahivo	ACTION REQUIRED	sibness	skinking	evoke
Sheet1

Cell Formulas
Range	Formula
J2:J10	J2	=IF(G2<=F2,"NO ACTION",IF(G2>F2,"ACTION REQUIRED"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J2:J10	Cell Value	beginning with "ACTION REQUIRED"	text	NO
J2:J10	Cell Value	contains "NO ACTION"	text	NO

how do you relay that into vba code though? I have not really found good resources that explain converting from a formula in a cell into VBA code or how to run the conditional formatting from the normal formula into VBA Code

 

[rlv01]

The simplest way to to use the macro recorder to record yourself creating the conditional formatting rules that you want, then study the recorder-produced VBA code and apply it to whatever macro you are working on.

 

[dreid1011]

Perhaps something like this for VBA in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LRow As Long, i As Long

LRow = Range("F" & Rows.Count).End(xlUp).Row

Application.EnableEvents = False
If Not Intersect(Target, Range("F2:G" & LRow)) Is Nothing Then
    For i = 2 To LRow
        If Range("F" & i).Value = Range("G" & i).Value Or Range("F" & i).Value > Range("G" & i).Value Then Range("J" & i).Interior.Color = vbGreen: Range("J" & i).Value = "NO ACTION"
        If Range("F" & i).Value < Range("G" & i).Value Then Range("J" & i).Interior.Color = vbYellow: Range("J" & i).Value = "ACTION REQUIRED"
    Next i
End If
Application.EnableEvents = True

End Sub