Getting special formatting to apply to all worksheets

[Idgarad]

The following is my attempt to color even days 1 color and odd days
another color.

Sub Hilite()

Dim lngRow As Long
Application.ScreenUpdating = False


For Each wks In Worksheets
lngRow = wks.Range("A2").End(xlDown).Row
Do While lngRow > 0
If Not IsEmpty(tempDay) Then
tempDay = Day(Cells(lngRow, 1).Value)

If tempDay Mod 2 = 0 Then
wks.Rows(lngRow).Interior.ColorIndex = 37
Else
wks.Rows(lngRow).Interior.ColorIndex = 36
End If
End If
lngRow = lngRow - 1
Loop
Next
Application.ScreenUpdating = True
End Sub


The problem is it works but applies the formatting to only the first
tab. What am I missing here?

Sample data below copied across 7 worksheets:

-sdfgsdfg 6/17/2007 19:43:22 6/17/2007 19:45:25
sdfgsdfg 6/17/2007 22:07:45 6/17/2007 22:08:19
fghjfgj 6/17/2007 22:15:04 6/17/2007 22:15:25
rtuyru 6/18/2007 4:00:09 6/18/2007 4:12:37
ryjyr 6/18/2007 4:31:51 6/18/2007 4:43:49
vbngh 6/18/2007 19:00:44 6/18/2007 19:01:16
vhnvhn 6/18/2007 19:07:37 6/18/2007 19:07:57
vmhvmv 6/19/2007 4:00:06 6/19/2007 4:13:02
dhgdtr 6/19/2007 4:33:16 6/19/2007 4:45:13
nmfyy 6/19/2007 19:01:09 6/19/2007 19:01:45
dthdth 6/19/2007 19:09:11 6/19/2007 19:09:32
dthdg 6/20/2007 4:00:07 6/20/2007 4:13:35
cvhncnb 6/20/2007 4:38:32 6/20/2007 4:50:31
cnccg 6/20/2007 19:00:56 6/20/2007 19:01:49
cgnct 6/20/2007 19:09:44 6/20/2007 19:10:05
cntctnctn 6/21/2007 4:00:11 6/21/2007 4:16:25
cntctnct 6/21/2007 4:44:37 6/21/2007 4:56:37
5yd5hh 6/21/2007 19:00:56 6/21/2007 19:01:44
d5hdg 6/21/2007 19:11:37 6/21/2007 19:11:58
dngdnd 6/22/2007 4:00:11 6/22/2007 4:14:19
cngcn 6/22/2007 4:57:18 6/22/2007 5:09:22
ccgncg 6/22/2007 19:00:52 6/22/2007 19:01:29
dth5 6/22/2007 19:08:21 6/22/2007 19:08:41
dh5dhd 6/23/2007 21:00:06 6/23/2007 21:12:18

 

[Vasant Nanavati]

1. You need to qualify each range reference with "wks."

2. Why don't you just use conditional formatting?
_____________________________________________________________________

 

[Steve the large]

You don't need to use vba code to do this.

copy the text into an excel sheet starting in cell B1, select all rows and
use "text to columns..." to parse the data into separate columns. I assume
the dates will be in column C after the parsing is done.

In column A1, enter the following formula:

=mod(int(C1),2)

This will generate a 1 or a zero depending on the value in Cell C1 (even or
odd day)
copy this formula down using the fill-down method. Now you have 0,1,0,1,...
depending on whether the day is even or odd.

Now select cell C1 and use conditional formatting. (Under the format menu)

Select "Formula Is" and in the box type the following
=A1=1
and then click on format and set the format you want for odd days.

Then click Add...
and select "Formula is" again, but this time type =A1=0

now set the format for an even day.

Using variations on this, you can pre-create the formulas and conditional
formats in one sheet, and copy over data in another. (one way to do it -there
are many)

If you really want to do it with VBA, I'll get back to this post later and
give you the code. But you have to tell me how you are importing the text
data into a spreadsheet and what version of XL you are using.






Background.
If the dates are in a column, and are not text. (a date is a number with
days to the left of the decimal pt, and time of day to the right of the
decimal point in the excell date format. This number is then displayed in
one of many different ways by selecting the appropriate number format.)

 

[Steve the large]

Oops, did not read your whole post, sorry.

There are several problems with the code you have listed -it can't work as
written.

(1) tempday is used before it has a value assigned.
(2) you have column 1 being used to get the day from, it should be column 2

I corrected these errors, and ran the code with the data you provided copied
into sheet1, sheet2, & sheet3 and it worked for all sheets.

Here is the code I ran:

Sub Hilite()

Dim lngRow As Long
Application.ScreenUpdating = False

For Each wks In Worksheets
lngRow = wks.Range("A2").End(xlDown).Row
Do While lngRow > 0
tempDay = Day(Cells(lngRow, 2).Value)
If Not IsEmpty(tempDay) Then
If tempDay Mod 2 = 0 Then
wks.Rows(lngRow).Interior.ColorIndex = 37
Else
wks.Rows(lngRow).Interior.ColorIndex = 36
End If
End If
lngRow = lngRow - 1
Loop
Next
Application.ScreenUpdating = True
End Sub

Try using the debugger and breaking on the first line, then step through the
code using the "F8" key. But it should work.

 

[Idgarad]

Oops, did not read your whole post, sorry.

There are several problems with the code you have listed -it can't work as
written.

(1) tempday is used before it has a value assigned.
(2) you have column 1 being used to get the day from, it should be column 2

I corrected these errors, and ran the code with the data you provided copied
into sheet1, sheet2, & sheet3 and it worked for all sheets.

Here is the code I ran:

Sub Hilite()

Dim lngRow As Long
Application.ScreenUpdating = False

For Each wks In Worksheets
lngRow = wks.Range("A2").End(xlDown).Row
Do While lngRow > 0
tempDay = Day(Cells(lngRow, 2).Value)
If Not IsEmpty(tempDay) Then
If tempDay Mod 2 = 0 Then
wks.Rows(lngRow).Interior.ColorIndex = 37
Else
wks.Rows(lngRow).Interior.ColorIndex = 36
End If
End If
lngRow = lngRow - 1
Loop
Next
Application.ScreenUpdating = True
End Sub

Try using the debugger and breaking on the first line, then step through the
code using the "F8" key. But it should work.












- Show quoted text -

Well that sort of works but now here is the problem. Change the other
tabs dates around and all that is happening is the coloring is being
based off the first sheet only. So the highlighting is correct on the
first tab but all the other tabs are wrong if the data isn't an exact
match of the first tab.

 

[Steve the large]

thats because the following line
without the "wks." the Cells() property is referring, correctly, to the
currently active sheet, which doesn't change just because wks does.

 

[Idgarad]

thats because the following line


without the "wks." the Cells() property is referring, correctly, to the
currently active sheet, which doesn't change just because wks does.






- Show quoted text -

Fixed and working great. Thank you, the help is greatly appreciated.