Change Text Colour if Value is in a List Q

[Sean]

How would I change the Text colour of a cell to white (invisible) if
that Value in the cell is listed within a list?

For example I have a range of values in Sheet1 from A5:W30, if any of
these cells is contained within the list that is detailed on Sheet2
A1:A5, then I want to change those values in Sheet1 A5:W30 to White

Thanks

 

[Dave Peterson]

Select Sheet2 A1:A5
Insert|Name
Call it
MyList

Select Sheet1 A5:W30
With A5 the activecell
Format|conditional formatting
Formula is:
=countif(myList,a5)>0

And give it a nice format (white on white)

 

[Guest]

Try something like:

Sub set_color()
Set r1 = Sheets("Sheet1").Range("A5:W30")
Set r2 = Sheets("Sheet2").Range("A1:A5")

For Each r2i In r2
v = r2i.Value
For Each r1i In r1
If r1i.Value = v Then
r1i.Font.ColorIndex = 2
End If
Next
Next

End Sub

 

[Sean]

Try something like:

Sub set_color()
Set r1 = Sheets("Sheet1").Range("A5:W30")
Set r2 = Sheets("Sheet2").Range("A1:A5")

For Each r2i In r2
v = r2i.Value
For Each r1i In r1
If r1i.Value = v Then
r1i.Font.ColorIndex = 2
End If
Next
Next

End Sub
--
Gary's Student
gsnu200707







- Show quoted text -

Thanks guys for the 2 options

 

[Sean]

Try something like:

Sub set_color()
Set r1 = Sheets("Sheet1").Range("A5:W30")
Set r2 = Sheets("Sheet2").Range("A1:A5")

For Each r2i In r2
v = r2i.Value
For Each r1i In r1
If r1i.Value = v Then
r1i.Font.ColorIndex = 2
End If
Next
Next

End Sub
--
Gary's Student
gsnu200707







- Show quoted text -

Just a slight advancement on this. How would I add up the instance of
my "white values" in a range. Currently I use the formula below, which
looks for the value in A43 of how many times it appears in D9 to AG36,
but now I want to do the same but only if they are coloured white
text

=COUNTIF($D$9:$AG$36,A43)

Sorry if I have posted to wrong Group

 

[Guest]

Sub sean2()
Set r = Range("D9:AG36")
whitecount = 0
For Each rr In r
If rr.Font.ColorIndex = 2 Then
whitecount = whitecount + 1
End If
Next
MsgBox (whitecount)
End Sub

 

[Sean]

Sub sean2()
Set r = Range("D9:AG36")
whitecount = 0
For Each rr In r
If rr.Font.ColorIndex = 2 Then
whitecount = whitecount + 1
End If
Next
MsgBox (whitecount)
End Sub
--
Gary''s Student
gsnu200707






- Show quoted text -

Thanks Gary, not quite what I was looking for, although I now realise
there is no worksheet function based on cell text colour so you must
use VB. Your code will work but instead of getting a total of all
White text, I want a number of values returned in a cell, not a
message box. A43 in my formula is a value that appears in D9:AG36, so
I wish total the number of white instances of this value in the range
D9:AG36