Loop Through Rows and Highlight Cells

[rob51852]

Hi,

I have a sheet that contains 3 rows of data. The top row contains benchmark data and the other two rows are results. The XL2BB is below.

I would like a macro to loop through the two results rows and highlight any cell (by using a different colour fill) that is 5% greater than the benchmark value in the same column.

Is this possible?

Thanks

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
56	162.98	6.54	24.93	135.92	16.05	148.57	6.14	24.18	134.73	16.33	24.74	142.71	18.23	24.35	124.67	13.21	30.31	132.60	19.87	26.45	128.94	13.14	17.02	162.17	19.31	243.00	115.00	46.85	51.10	2.05
57	156.75	6.75	23.22	130.63	15.10	145.38	6.88	21.15	128.08	14.98	22.78	137.58	16.97	21.35	119.03	12.61	29.77	113.72	16.32	18.18	117.40	11.67	25.40	191.27	22.59	168.00	149.00	50.00	50.00	0.00
58	157.75	6.75	24.22	120.63	14.10	145.38	7.88	21.15	129.08	13.98	22.78	137.58	16.97	21.35	129.03	22.61	29.77	113.72	26.32	18.18	113.40	11.67	21.40	191.27	25.59	162.00	149.00	51.00	53.00	0.00
Summary

 

[Fluff]

Why not just use conditional formatting?

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
56	162.98	6.54	24.93	135.92	16.05	148.57	6.14	24.18	134.73	16.33	24.74	142.71	18.23	24.35	124.67	13.21	30.31	132.6	19.87	26.45	128.94	13.14	17.02	162.17	19.31	243	115	46.85	51.1	2.05
57	156.75	6.75	23.22	130.63	15.1	145.38	6.88	21.15	128.08	14.98	22.78	137.58	16.97	21.35	119.03	12.61	29.77	113.72	16.32	18.18	117.4	11.67	25.4	191.27	22.59	168	149	50	50	0
58	157.75	6.75	24.22	120.63	14.1	145.38	7.88	21.15	129.08	13.98	22.78	137.58	16.97	21.35	129.03	22.61	29.77	113.72	26.32	18.18	113.4	11.67	21.4	191.27	25.59	162	149	51	53	0
Data

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A57:AD58	Expression	=A57>=A$56*1.05	text	NO

 

[sykes]

Rather than VBA, wouldn't conditional formatting be a lot easier, for that?

 

[rob51852]

Thanks guys. There are a few macros in the sheet that are run by a user clicking a button and I'd like to add a macro to that if possible.

 

[kevatarvind]

Hi Pls try below code

Sub Highlight_Cells()
    Dim lc As Long
    Dim c As Range
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    For Each c In Range(Cells(2, 1), Cells(3, lc))
        If c.Value - Cells(1, c.Column).Value >= 5 Then
            c.Interior.Color = vbGreen
        End If
    Next
End Sub

 

[Fluff]

Ok, how about

Sub rob()
   Dim r As Long, c As Long
   
   For r = 57 To 58
      For c = 1 To 30
         If Cells(r, c) >= Cells(56, c) * 1.05 Then Cells(r, c).Interior.Color = 45678
      Next c
   Next r
End Sub

 

[rob51852]

Thanks guys, these are great.

 

[Fluff]

Glad we could help & thanks for the feedback.