Macro Conditional Formatting

[howard]

I have the following code to format data using CF. Where the value in Col J from row 12 onwards is >=30, then I need , to highlight from column A to J and 2 rows below the value conating a value in Col J that is 30 and greater

For Eg if J12 is say 35 then A12 to A14 must be highlighted in Gray and so on i.e for each value that is >=30 Col A to J + 2 rows below the column containing a value >= 30 must be highlighted using CF

For some unkown reason, this is not happening

Sub conditional_formating()
Sheets("Sheet1").Select
Finalrow = Range("a12").End(xlDown).Row
With Range("A12:J" & Finalrow)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$J12>=30"
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.Resize(2).FormatConditions(1).Interior.ColorIndex = 15
End With
End Sub

It would be appreciated if someone could assist-see sample data after running Macro and what sample data should look like

Sample Data After running conditional Formatting

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	*	12/09/2011	*	*	*	*	*	*	*	*
2	12/09/2011 15:06	*	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	Y	W	*
6	*	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*
8	REF	Type	Date	StoZk No.	Amount	Vehicle/Customer	Parts	Labour	Other	Age
9	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*
12	ZZ3815	ZEB	01/09/2011	ZBK448EK	0	0	0	*	*	35
13	*	*	*	1.4	AMBIENTE	0	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*
16	ZZ3819	ZEB	01/09/2011	ZHJ290EK	0	0	0	*	*	12
17	*	*	*	2.5TZ	4X2	D/C	*	0	*	*
18	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
20	ZZ3835	ZEB	02/09/2011	ZXW724EK	0	0	0	*	*	35
21	*	*	*	130	STING	SEDAN	*	0	*	*
22	*	*	*	*	*	*	*	*	*	*
23	*	*	*	*	*	*	*	*	*	*
24	ZZ3838	ZEB	02/09/2011	ZLR803EK	0	0	0	*	*	41
25	*	*	*	0	0	*	*	*	*	*
26	*	*	*	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*	*	*	*
28	ZZ3847	ZEB	05/09/2011	ZXZ155EK	0	0	0	*	*	48
29	*	*	*	0	0	*	*	*	*	*
30	*	ZEL	*	*	*	*	*	*	*	*
31	*	*	*	*	*	*	*	*	*	*
32	ZZ3848	ZEB	05/09/2011	ZLR109EK	0	0	0	*	*	18
33	*	*	*	0	0	*	*	*	*	*
34	*	ZEL	*	*	*	*	*	*	*	*
35	*	*	*	*	*	*	*	*	*	*
36	ZZ0779	ZEL	14/12/2010	BRL271B	29365.1	0	20	*	*	153
37	*	*	*	3	XLT	SUPER	0	29385.1	*	*
38	*	ZEL	*	GARAGE	BH	*	*	*	*	*
39	*	*	*	*	*	*	*	*	*	*
40	ZZ2464	ZEL	11/05/2011	ZSZ391EK	0	0	300	*	*	105
41	*	*	*	ZOZUS	1.8TDCI	T	0	300	*	*
42	*	ZEL	*	KITZHING	*	*	*	*	*	*
Sheet1

Sample Data-Manually coloring data to show wehat it should lok like if running conditional formatting

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	*	12/09/2011	*	*	*	*	*	*	*	*
2	12/09/2011 15:06	*	*	*	*	*	*	*	*	*
3	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*
5	*	*	*	*	*	*	*	Y	W	*
6	*	*	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*	*	*
8	REF	Type	Date	StoZk No.	Amount	Vehicle/Customer	Parts	Labour	Other	Age
9	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*
12	ZZ3815	ZEB	01/09/2011	ZBK448EK	0	0	0	*	*	35
13	*	*	*	1.4	AMBIENTE	0	*	*	*	*
14	*	*	*	*	*	*	*	*	*	*
15	*	*	*	*	*	*	*	*	*	*
16	ZZ3819	ZEB	01/09/2011	ZHJ290EK	0	0	0	*	*	12
17	*	*	*	2.5TZ	4X2	D/C	*	0	*	*
18	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
20	ZZ3835	ZEB	02/09/2011	ZXW724EK	0	0	0	*	*	35
21	*	*	*	130	STING	SEDAN	*	0	*	*
22	*	*	*	*	*	*	*	*	*	*
23	*	*	*	*	*	*	*	*	*	*
24	ZZ3838	ZEB	02/09/2011	ZLR803EK	0	0	0	*	*	41
25	*	*	*	0	0	*	*	*	*	*
26	*	*	*	*	*	*	*	*	*	*
27	*	*	*	*	*	*	*	*	*	*
28	ZZ3847	ZEB	05/09/2011	ZXZ155EK	0	0	0	*	*	48
29	*	*	*	0	0	*	*	*	*	*
30	*	ZEL	*	*	*	*	*	*	*	*
31	*	*	*	*	*	*	*	*	*	*
32	ZZ3848	ZEB	05/09/2011	ZLR109EK	0	0	0	*	*	18
33	*	*	*	0	0	*	*	*	*	*
34	*	ZEL	*	*	*	*	*	*	*	*
35	*	*	*	*	*	*	*	*	*	*
36	ZZ0779	ZEL	14/12/2010	BRL271B	29365.1	0	20	*	*	153
37	*	*	*	3	XLT	SUPER	0	29385.1	*	*
38	*	ZEL	*	GARAGE	BH	*	*	*	*	*
39	*	*	*	*	*	*	*	*	*	*
40	ZZ2464	ZEL	11/05/2011	ZSZ391EK	0	0	300	*	*	105
41	*	*	*	ZOZUS	1.8TDCI	T	0	300	*	*
42	*	ZEL	*	KITZHING	*	*	*	*	*	*
Sheet1

 

[asking]

Hello Howard,

Your errors are your $J12 should be $J$12 and you need to offset the end down by -1

If you want it to look like what you did manually change the -1 to -2 After Row in the code.

Try this code revision ~

Sub conditional_formating()
Sheets("Sheet1").Select
Finalrow = Range("a12").End(xlDown).Row - 1

With Range("A12:J" & Finalrow)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$J$12>=30"
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.Resize(2).FormatConditions(1).Interior.ColorIndex = 15
End With
End Sub

Cheers

 

[asking]

Hello again,

Just thought you probably wish to do the whole sheet format thing in one hit.

If you do, try using a helper column that is unused. For my example in the code I am using column AC

In Cell AC12 Copy and paste this formula
=IF(ISNUMBER(J12),J12,IF(ISNUMBER(J13),1,AC11))
Now fill that down for the rows that you have data in.

If you have more than 1000 rows of data change this bit of code to suit
With Range("A12:J1000")

Then try this code ~

Sub conditional_formating()
Sheets("Sheet1").Select
With Range("A12:J1000")
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$AC12>=30"
.FormatConditions(1).Interior.PatternColorIndex = xlAutomatic
.Resize(2).FormatConditions(1).Interior.ColorIndex = 15
End With
End Sub

Hope that works for you.

 

[howard]

Thanks for the help, much appreciated