Change Text Colour if Value is in a List Q
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 |
Change Text Colour if Value is in a List Q
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 |
Change Text Colour if Value is in a List Q
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 |
Change Text Colour if Value is in a List Q
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 |
Change Text Colour if Value is in a List Q
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 |
Change Text Colour if Value is in a List Q
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 |
Change Text Colour if Value is in a List Q
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 |
All times are GMT +1. The time now is 02:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com