PDA

View Full Version : [SOLVED] Highlighting every other row containing data



oam
10-06-2016, 05:35 PM
I have search and cannot find a code or conditional formatting formula that will highlight every other row with multiple columns (A4:BB250) only if the rows contain data.

Does anyone know of a way to do this?

Thank you for your help

SamT
10-06-2016, 05:55 PM
Maybe something like this

sub VBAX_SamT()
dim r as long
dim ColorMe as boolean
dim WsF AS object
set WsF = application.worksheet.function

ColorMe = True 'to start with a highlighted row

for r = 4 to 250' or 'To Cells(Rows.Count, "BB").End(xlUp).Row
if WsF.Count(Rows.(r)) and ColorMe then ' Count? or CountA?
range("A" & r & ":BB" & r). interior.colorIndex = 5 'Adjust color to suit
ColorMe = not ColorMe
end if
next r
end sub

oam
10-06-2016, 06:39 PM
SamT,

Thank you a quick response.

I get an error when I run the code "Application does not support this property or method" Also, I get an error on this line but they maybe related.
Thank you again for your help


WsF.Count(Rows.(r))ColorMe Then' Count? or CountA?

YasserKhalil
10-06-2016, 10:56 PM
Just remove dot from this line after worksheet

Set WsF = application.worksheet.function

And also remove dot from this line after rows

If WsF.Count(Rows.(r)) And ColorMe Then ' Count? or CountA?

YasserKhalil
10-06-2016, 11:02 PM
May be (if you mean to color only the row if all cells in the same row has data)

Sub Test()
Dim r As Long
Dim WsF As Object

Set WsF = Application.WorksheetFunction

For r = 4 To 250
If WsF.CountA(Rows(r)) = 54 Then
Range("A" & r & ":BB" & r).Interior.ColorIndex = 5
End If
Next r
End Sub

SamT
10-07-2016, 06:22 AM
I get an error when I run the code "Application does not support this property or method" Also, I get an error on this line

With Property and Method errors, first, always look at the error line for typos, misspellings, missing or extra dots and other punctuation.

With "A Without B" errors, check for missing and mismatched "End"s and "Next"s in the code around the error line

Bob Phillips
10-07-2016, 10:36 AM
I have search and cannot find a code or conditional formatting formula that will highlight every other row with multiple columns (A4:BB250) only if the rows contain data.

Does anyone know of a way to do this?

Thank you for your help

=MOD(ROW(),2)=0

oam
10-11-2016, 07:38 PM
Thank you all for your help.

Tom Jones
10-12-2016, 01:08 AM
XLD you did not read carefully requirement.


http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by oam http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=350443#post350443) I have search and cannot find a code or conditional formatting formula that will highlight every other row with multiple columns (A4:BB250) only if the rows contain data.



=MOD(ROW(),2)=0