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

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)

Sean wrote:

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

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


"Sean" wrote:

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

On Feb 24, 2:27 pm, Gary''s Student
wrote:
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



"Sean" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks guys for the 2 options

On Feb 24, 2:27 pm, Gary''s Student
wrote:
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



"Sean" wrote:
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- Hide quoted text -

- 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

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


"Sean" wrote:

On Feb 24, 2:27 pm, Gary''s Student
wrote:
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



"Sean" wrote:
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- Hide quoted text -

- 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

On Feb 24, 3:51 pm, Gary''s Student
wrote:
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



"Sean" wrote:
On Feb 24, 2:27 pm, Gary''s Student
wrote:
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

"Sean" wrote:
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- Hide quoted text -

- 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- Hide quoted text -

- 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