question regarding dynamic cell colors
Sub ColorActiveSheet()
Dim cell as Range
for each cell in Range("A2").Resize(1,225)
if lcase(cell.Value) = s then
cell.Resize(29,1).Interior.ColorIndex = 15
end if
Next
End Sub
--
Regards,
Tom Ogilvy
wrote in message
oups.com...
Tom,
Thanks for the response. Of course, you're correct. I am only using
225 columns *per sheet*. Sorry for the confusion.
The position of each day of the week changes by design from month to
month. It's dynamic and based on user input.
For example, range B2:C30 should be gray if B2 and C2 = "S". Next
month, C2:D30 may need to be gray if C2 and D2 = "S". It's dynamic by
design.
Thanks greatly for your input. You've helped me before, and it's
always appreciated.
J
Tom Ogilvy wrote:
There are only 256 columns in a worksheet. So 29 rows by 450 columns
would
be difficult.
You can't progran
for each occupied cell in a row near the top
so why not specify where your data is located and where the row is that
contains the days of the week.
--
Regards,
Tom Ogilvy
wrote in message
oups.com...
Excel version: Excel 2003 Pro SP2
Overview: I have created an Excel work schedule that displays days of
the week along a single row near the top. (e.g. S M T W T F S [...]
etc.) All additional rows in the array consist of various formulas.
The entire array is 29 rows by 450 columns.
Issue: In the row that displays days of the week, I want to find
*all* cells that contain "S" (for Saturday and Sunday). Then, I
want to change the cell color for those days to gray, and I also want
the entire range in the column beneath each "S" to be shaded gray
as well.
I realize that conditional formatting is not a solution in this case.
I am hoping that someone with VBA experience has already addressed
this
issue and developed a viable solution.
The final product should show all weekend columns displayed as gray.
Obviously, I do not want the gray cell coloring to extend beyond the
bottom or top of the array, either.
Thanks kindly for sharing your ideas and ingenuity.
Best Regards,
J
|