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
<GarysStud...@discussions.microsoft.com> 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
<GarysStud...@discussions.microsoft.com> 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
> <GarysStud...@discussions.microsoft.com> 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
<GarysStud...@discussions.microsoft.com> 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
> > <GarysStud...@discussions.microsoft.com> 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