|
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 |
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 |
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 |
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 |
Getting special formatting to apply to all worksheets
thats because the following line
tempDay = Day(Cells(lngRow, 2).Value) should be tempDay = Day(wks.Cells(lngRow, 2).Value) without the "wks." the Cells() property is referring, correctly, to the currently active sheet, which doesn't change just because wks does. "Idgarad" wrote: On Jun 27, 2:38 pm, Steve the large wrote: 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- Hide quoted text - - 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. |
Getting special formatting to apply to all worksheets
On Jun 28, 1:04 pm, Steve the large
wrote: thats because the following line tempDay = Day(Cells(lngRow, 2).Value) should be tempDay = Day(wks.Cells(lngRow, 2).Value) without the "wks." the Cells() property is referring, correctly, to the currently active sheet, which doesn't change just because wks does. "Idgarad" wrote: On Jun 27, 2:38 pm, Steve the large wrote: 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- Hide quoted text - - 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.- Hide quoted text - - Show quoted text - Fixed and working great. Thank you, the help is greatly appreciated. |
| All times are GMT +1. The time now is 02:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com