Row Color

[Guest]

I am looking to change a printable area row if a condition is true such as :

If any cell or cells within the range of AL6:AL16 contains an "A" then
change the row containing that "A" (Only the printable area) to the color
orange. If not leave as is

Is this possable?

 

[Tom Ogilvy]

How would we know the printable area? How are you defining it?

 

[Guest]

Tom,

The printable areas are Page 1 - is B616 and page 2 is S6:AH16

Thanks Tom

 

[Tom Ogilvy]

Dim cell as Range, rng as Range
for each cell in Range("AL6:AL16")
if cell.Value = "A" then
set rng = Intersect(Range("B616, S6:AH16"), _
cell.EntireRow)
rng.Interior.ColorIndex = 45
end if
Next

 

[Guest]

Tom I tried to copy and past into a module, however I got and "Invalid
outside procedure " error. Do I need a Function prior to the Dim Statement?

 

[Guest]

Nevermind the comment about the error, I got the VBA to work, However I have
numerous sheets for this to cover and it seems the way it is written now, the
only way it will work is to open each sheet. Is there a way to have is run on
all sheets as soon as the workbook is open?

Once again Thanks for your time!

 

[Tom Ogilvy]

Sub MyMacro()
Dim cell as Range, rng as Range
for each cell in Range("AL6:AL16")
if cell.Value = "A" then
set rng = Intersect(Range("B616, S6:AH16"), _
cell.EntireRow)
rng.Interior.ColorIndex = 45
end if
Next
End Sub

 

[Guest]

Is there anyway I can have this macro run automatically when the workbook is
open for each sheet in the workbook?

 

[Tom Ogilvy]

Private Sub Workbooks_Open()
Dim cell as Range, rng as Range
Dim sh as Worksheet
for each sh in ThisWorkbook.Worksheets
for each cell in sh.Range("AL6:AL16")
if cell.Value = "A" then
set rng = Intersect(sh.Range("B616, S6:AH16"), _
cell.EntireRow)
rng.Interior.ColorIndex = 45
end if
Next
Next
End Sub

go to the VBE and in the project explorer, double click on the ThisWorkbook
entry in your project. In the resulting module, in the left drodown (at
the top) select Workbook and in the right select Open. This is where you
want to place the code so it looks like the above.

 

[Guest]

Thanks Tom,

this is exactly what I was looking for.