PDA

View Full Version : [SOLVED] VBA Conditional Format Every Other Row



brorick
11-15-2004, 01:25 PM
Is it possible to create VBA coding to apply conditional formatting to every other row that contains data? So, if I have data in A1:G500, I would hope to have every other row upto column G, highlighted with a yellow color. I know I can accomplish this within Format, Conditional Formatting, but I would rather use VBA. Plus, I would like to have the worksheet automatically highlight any new rows. Any help is greatly appreciated. Thank you in advance.

Zack Barresse
11-15-2004, 02:23 PM
Hi brorick,

From what I gather:

You only want columns A:G highlighted.

You only want rows 1:500 highlighted.

In said field, you want to go down a column and highlight every other field with data.

Okay, some questions:
Does it matter what row to start coloring on? Odd/Even?
What if there is no data in that row?
Do you want that row highlighted if there is any data in that row, or a specific column (e.g. col A)?

brorick
11-15-2004, 02:30 PM
Thank you for your response.

Does it matter what row to start coloring on? Odd/Even?
Answer: No

What if there is no data in that row?
Do you want that row highlighted if there is any data in that row, or a specific column (e.g. col A)?
Answer: All rows should contain data. But, if for some reason, the list is sorted which results in a blank row in the middle of the list, it would be nice to keep the same color theme. Every other row colored from row one to the last row that contains data regardless of blank rows.

Firefytr, thank you for your help.

Zack Barresse
11-15-2004, 03:05 PM
Then you may be looking at something like this ...


Option Explicit

Sub colorEveryOtherRow()
Dim i As Long, myRow As Long, isC As Boolean
isC = True
For i = 1 To 500
myRow = WorksheetFunction.CountA(Range("A" & i & ":G" & i))
Select Case isC
Case True: isC = False
Case False: isC = True
End Select
If isC = True Then
If myRow <> 0 Then
Range("A" & i & ":G" & i).Interior.ColorIndex = 6
Else
If i <> 1 Then
Range("A" & i & ":G" & i).Interior.ColorIndex = _
Range("A" & i - 1).Interior.ColorIndex
Else
Range("A" & i & ":G" & i).Interior.ColorIndex = 0
End If
End If
Else
If myRow = 0 Then
If i <> 1 Then
Range("A" & i & ":G" & i).Interior.ColorIndex = _
Range("A" & i - 1).Interior.ColorIndex
Select Case isC
Case True: isC = False
Case False: isC = True
End Select
Else
Range("A" & i & ":G" & i).Interior.ColorIndex = 0
End If
End If
End If
Next i
End Sub

Sub unColorEveryOtherRow()
Range("A1:G500").Interior.ColorIndex = 0
End Sub


If you wanted to do every other row no matter what was in the cells you could use regular conditional formatting for that (Formula Is) ...



=MOD(ROW(),2)=0



HTH

TonyJollans
11-15-2004, 03:06 PM
Select the range you want striped, and ..

Format > Conditional Formatting
Formula Is

=MOD(ROW(),2)=1
Choose your colour, and it's done

It should be easy enough in code to do something like ..



With Range("A1").CurrentRegion
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
.FormatConditions(1).Interior.ColorIndex = 3
End With

Anne Troy
11-15-2004, 04:42 PM
You guys NEVER cease to amaze me.

:drink:

Zack Barresse
11-15-2004, 04:53 PM
You guys NEVER cease to amaze me.

:drink:

We aim to please! :D

TonyJollans
11-15-2004, 05:07 PM
She said Amaze - not Please - they're not the same :rofl

Anne Troy
11-15-2004, 05:15 PM
LOL! I beg to differ!! Sometimes they ARE!! ROFLMFAO!!!

brorick
11-16-2004, 12:48 PM
Firefytr, the code worked perfectly. You have ended my long search for the right code and I really appreciate your help. Also, I would like to thank TonyJollans for the additional code.

You guys and gals are the reason I come to this forum. You are always so willing to help and very quick to respond. I also enjoy everyones witty humor. I prefer your forum to all other Excel forums. Have a great day!

Zack Barresse
11-16-2004, 12:54 PM
Thank you very much brorick! :D You just made my day! :yes

I'm glad to be of help to you. Come back anytime!

I'll go ahead and mark this Solved.

TonyJollans
11-16-2004, 01:32 PM
My thanks, also, for the kind words, brorick :D