Auto Color whole column when found text "Sunday"

[Kenji]

The code below works well also. However, I got 2 problems here.
First, I manage to find the word " Sunday", then color the cell below that Sunday. However, I need Excel auto find out Sunday and color that particular columns for me, for example, Sunday found and color start from cell(L55) below that Sunday up to more cells(L59), Range("L55:L59").
Second, I only manage to find first Sunday, I wish the system keep find and keep color untill it found that rows blank, so I using this code.
'Encountered blank cell in row 2, terminate search
If Len(Cells(2, LColumn)) = 0 Then
MsgBox "No matching date was found."
Exit Sub

I attached the excel files with sample, I really hope someone can help, these problems delay me 2 months d. Thanks alot !

Please Login or Register  to view this content.

[royUK]

You don't need VBA to do this. Conditional Formatting will work

See this

http://excel-it.com/excel_conditional_formatting.htm

Select a cell & using the Conditional Format dialog in the formula is box type

=B$2="Sunday"

Then use th Format painter to copy the Format to the other cells.

Can't see why you need this because you have fixed the text as the day of week, it won't change so you could just colour the cells manually

[Kenji]

I know that function, but I want use VBA so all things can be done with 1 button. I uploaded another Excel files, hope you can understand more. I will explain more in the excel, hope you all can help, Thanks a lot.

[royUK]

I cannot see the point in using VBA when an inbuilt Excel Function is available, Conditional Formatting would be quicke 7 more efficient than code

Looking at your exampleit is totally different to the first, the days are added by formulas. Why waste time adding a workbook that is different?

You still can do this with Conditional formatting, whereas your VBA will fail because Sunday is formatting not the actual cell's value.

You can change the Formula in B3 to =Text(b2,"dddd") then use this formula for CF

=B$3="Sunday"

Alternately, use this formula

=WEEKDAY(B$2,1)=1

[Kenji]

I get your idea of using CF. It pretty much easier. However, I might too stupid in that, can you teach me how to apply my condition as for example, i use formula =WEEKDAY(B2,1). If B2 is Sunday means value = 1(I dunno this is cells value or formula value), then highlight whole column form B2 till B32 with grey. I using Ms Excel 2003. I keep play with the formula, remove $ or add , play with the = or greater sign, I still can't manage to get what condition I want. Please help me on this, Thanks .