Making a row coloured if spacific data is anywhere in it

Making a row coloured if spacific data is anywhere in it

Post by DJ M » Thu, 06 Sep 2007 23:56:50


HI, im stuck on a bit of VBA for my little program i've written and
need some help!

I have a sheet of data which uses vlookup on another sheet to get 3
columns of information onto sheet 1, this works fine and also copies
and pastes special. the problem i have is if #NAME? appears in any
cell on that row the row needs to be filled with, or, the text turnt
into red.
If #NAME? doesnt appear and all the data is looked up then the row or
text in the row should appear green.

Now, this is where it gets a little more complicated as 1 of the
columns must take priority over another 1, say if LookedUp column 1
has data in it but LookedUp column 2 doesnt, thats fine so the row
should be coloured green.
if LookedUp column 1 has NO data and columns 2 OR 3 has NO data then
the row should be red.
if LookedUp column 1 has NO data and columns 2 AND 2 HAS data then the
row should be green.

If anyone cannot grasp the concept please dont worry ANY help is
appreciated :)
 
 
 

Making a row coloured if spacific data is anywhere in it

Post by Dave D- » Fri, 07 Sep 2007 05:13:42

D-C writes:
Dare I suggest the straight-arrow:
Sub Sub1(pRow&)
Dim iColorIndex%
If Cells(pRow, 1) <> "#NAME?" Then
iColorIndex = 4 ' green
ElseIf Cells(pRow, 2) <> "#NAME?" And Cells(pRow, 3) <> "#NAME?"
Then
iColorIndex = 4 ' green
Else
iColorIndex = 3 ' red
End If
Range(Cells(pRow, 1), Cells(pRow, 3)).Interior.ColorIndex =
iColorIndex
End Sub




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.yqcomputer.com/ The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----