changing cell color in named range...

[sadams1]

hello & thank you for considering this insanity I'm looking to do!

In the screenshots...
#1 shows a "named range" I'd like to have the rows filled with a light gray.
#2 shows random colors that could be in any of those cells in the named range
#3 shows the result I'd like to see which is to fill any cell with "no fill" to light gray


I have the following code which tests each cell in a range to see if it's gray & if so, change to "no fill"
Would it be possible to pass a "named range" into this code to accomplish #3 from above.

Many thanks for your time!
Steve




Sub TestFillColor()

Dim cell As Range

'Loop through each cell in selected range
For Each cell In Selection.Cells

'Test if cell has a fill color
If cell.Interior.Color = RGB(242, 242, 242) Then

'change to no fill
cell.Interior.Color = RGB(255, 255, 255)

End If

Next cell

End Sub

 

[sadams1]

here are two approaches I'm messing with & getting errors under the "Loop through each cell in selected range" comment...the 1st specifies the range & the 2nd specifies the "named range".

1. For Each cell In Selection.Cells("A5:NM5")

2.
Range("ZZZ").Select
Application.Goto "ZZZ"


It looks like this either would work but something is wrong...any insights would be great...thanks!

 

[sadams1]

I hacked away & came up with the clunky thing but it works!

***********this is 1 of 2...the range is a contiguous and must be defined***

Sub RemoveGray()

Dim cell As Range

Range("ZZZ").Select
Application.Goto "ZZZ"


'Loop through each cell in selected range
For Each cell In Selection.Cells

'Test if cell has a fill color
If cell.Interior.Color = RGB(242, 242, 242) Then
'255 = white

'change to no fill
cell.Interior.Color = RGB(255, 255, 255)
'242 = gray


End If

Next cell

End Sub




************this is 2 of 2...the range is non-contiguous & creates the "ledger paper" alternating colored rows
Sub AddAlternatingGrayRows()

Dim cell As Range

Range("BBB").Select
Application.Goto "BBB"




'Loop through each cell in selected range
For Each cell In Selection.Cells



'Test if cell has a fill color
If cell.Interior.Color = RGB(255, 255, 255) Then
'255 = white

'change to no fill
cell.Interior.Color = RGB(242, 242, 242)
'242 = gray


End If

Next cell

End Sub