How do I remove formatting from the non-active area of a spreadsheet?

[Zenwood]

I use this macro on many different spreadsheets and would like to remove the excess formatting. If you look at the sample spreadsheet you see the highlight goes row by row all the way across. I only want the formatting to apply to the active area. In the case of the sample spreadsheet columns A to C.

Can I modify the Macro to format the active area or can I remove the excess formatting?

Sub GroupRowA_byColor()


' 10.23.17



Dim h, i, j, k, s, r, g, b As Integer

h = 1 ' Find the end of this column
i = 1 ' Compare items in the column
j = 2 ' Start with this row
k = 240 ' Start with this color

s = k ' same color

r = 205 ' Base Red
g = 255 ' Base Green
b = 0 ' Base Blue

Do While (Cells(j, h) <> "")

If (Cells(j, i) <> "") And (Cells(j - 1, i) = Cells(j, i)) Then
k = k ' keep the color on match
Else

If k = s Then
k = k - 50 ' Change the color
Else
k = k + 50 ' Back to original color
End If

End If

Rows(j).Interior.Color = RGB(r, g, k)


j = j + 1

Loop

End Sub

-------
Spreadsheet sample

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Order[/TD]
[TD="class: xl63, width: 64"]Item[/TD]
[TD="class: xl63, width: 64"]Quantity[/TD]
[TD="class: xl64, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]stuff.1[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]stuff.1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"]stuff.1[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]stuff.2[/TD]
[TD="class: xl66"]4[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]stuff.2[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]stuff.2[/TD]
[TD="class: xl66"]6[/TD]
[TD="class: xl66"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]300[/TD]
[TD="class: xl65"]stuff.3[/TD]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

This is formatting the entire row

Rows(j).Interior.Color = RGB(r, g, k)

Change it to

Range("A" & j & ":C" & j).Interior.Color = RGB(r, g, k)

 

[cerfani]

Instead of using...

Rows(j).Interior.Color = RGB(r, g, k)

do...

Range(Cells(j, 1), Cells(j, 3)).Interior.Color = RGB(r, g, k)

 

[Zenwood]

This is formatting the entire row

Rows(j).Interior.Color = RGB(r, g, k)

Change it to

Range("A" & j & ":C" & j).Interior.Color = RGB(r, g, k)

Thank you!

The fix works the way I had hoped.

 

[Scott T]

You're welcome

 

[Zenwood]

Thank you. This also works.