Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Getting special formatting to apply to all worksheets
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default Getting special formatting to apply to all worksheets
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,080
Default Getting special formatting to apply to all worksheets
1. You need to qualify each range reference with "wks."

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

"Idgarad" wrote in message
ups.com...
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 21
Default Getting special formatting to apply to all worksheets
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.)




"Idgarad" wrote:

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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 21
Default Getting special formatting to apply to all worksheets
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" wrote:

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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Apply to all worksheets Brandi Excel Discussion (Misc queries) 0 November 14th 09 04:31 PM
apply arithmetic functions on special multiple cells Zhekka Excel Worksheet Functions 5 February 10th 06 02:04 AM
Won't apply to all worksheets G Setting up and Configuration of Excel 1 November 28th 05 05:56 PM
Find the nearest point to 1000 rows where "amounts" balance and apply special formatting to that row Paul Excel Programming 4 October 14th 05 05:02 PM
How does special formula apply to conditional formatting? Frances Excel Discussion (Misc queries) 1 August 22nd 05 01:09 PM


All times are GMT +1. The time now is 04:52 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement