Need some guidance on how to proceed

[Bub_the_Zombie]

Hello Excel Gurus!

I am still new to coding excel and i am not sure what I want to do is possible; and if it is possible how to start putting it together. This seems like it should be easy, but excel has been fooling me everytime I say that. I really need someone more familiar with excel to tell me how to proceed, and what I should be googling to make this happen.

I have 2 large tables that data is added and deleted throughout the day, and I want the table rows to highlight different colors when a value in a certain cell is present; But also have a way to toggle the highlight on and off.

I am bad at explaining things, below is the technical details that may make this make more sense.

Table 1 has a range of ("F4:X33") (Table 1 header is located ("F3:X3")
Row 34 is blank
Table 2 has a range of ("F36:X45")(Table 2 header is located ("F35:X35")
The data that will trigger the highlight is in column "U" for both tables, column "U" is not used for anything else on the Worksheet.

The data in column U is, and the highlight I would like them to have.
Brad = red highlight
Heather = green highlight
Athena = blue highlight
Tom = Light Gray highlight
""= No highlight

Then a way to turn the highlight on /off.

I am not sure if trying to make this into a toggle button using vba makes more sense, or if writing a formula could preform the function. Which ever one is more practical It needs to auto update as changes happen to the rows throughout the day.

The two tables are almost identical, but need to stay separate for another function that the worksheet needs to do.

Thoughts?

 

[Snakehips]

BtZ,

So the whole table row is highlighted if it's U contains one of the four names?

Is the toggle to apply to the two tables as a whole, highlighting / no highlighting?

 

[Bub_the_Zombie]

Yes the whole table row is highlighted it it contains one of the four names. (can this be done with multiple colors as noted in my example?)

The toggle is to apply highlighting / no highlighting, either 1 button for both tables or two separate if needed.

Is that doable?
I learnt a valuable lesson to ask before devoting lots of time to a project. In this worksheet had 200+ radio buttons, 100+ toggle buttons, etc... then I came across a way to get what I needed with a few formulas. Two weeks of coding vba fixed in 4 hours of coding formulas. Great learning experience tho.

 

[Snakehips]

One possibility:
Have an otherwise available cell that you can use as 'On' or "Off' for the highlighting toggle ?. (B1 below)
Use conditional formatting with a custom formula rule for each of the four names. (Example below shows rule for Brad, just change name for others)

To set rules to apply to both tables select both tables but select table2 first and then table 1 so that F4 becomes the active cell.
Then CF > New Rule >> Use formula... >> Set Fill Colour and repeat for all 4 names (rules)

Excel Workbook
	A	B	C	D	E	F
1	Toggle	On	*	*	*	*
2	*	*	*	*	*	*
3	*	*	*	*	*	*
4	*	*	*	*	*	*
Sheet6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F4	1. / Formula is =AND($B$1="On",$U4="Brad")	Abc

Hope that helps.

 

[C with no eyes]

1 Highlight column U.
2 Select: conditional formatting; highlight cells rules; equal to.
3 Type [name] e.g. brad
4 Select format from drop down menu or custom format (also in drop down) to edit fill
5 Select OK
6 repeat steps 2 to 5 for each name
7 With column U still highlighted select: conditional formatting; highlight cell rules; more rules; use a formula to determine which cells to format
8 Type: =$U$1="off"
9 Format: set fill to white ('no fill' will not work)
10 Select: conditional formatting; manage rules
11 Highlight the white fill formatting line and put it to the top of the list using the arrows next to the delete rule button

 

[C with no eyes]

1 Highlight column U.
2 Select: conditional formatting; highlight cells rules; equal to.
3 Type [name] e.g. brad
4 Select format from drop down menu or custom format (also in drop down) to edit fill
5 Select OK
6 repeat steps 2 to 5 for each name
7 With column U still highlighted select: conditional formatting; highlight cell rules; more rules; use a formula to determine which cells to format
8 Type: =$U$1="off"
9 Format: set fill to white ('no fill' will not work)
10 Select: conditional formatting; manage rules
11 Highlight the white fill formatting line and put it to the top of the list using the arrows next to the delete rule button

Sorry, forgot to say this will turn off the highlighting when the word off is typed in u1

 

[C with no eyes]

1 Highlight column U.
2 Select: conditional formatting; highlight cells rules; equal to.
3 Type [name] e.g. brad
4 Select format from drop down menu or custom format (also in drop down) to edit fill
5 Select OK
6 repeat steps 2 to 5 for each name
7 With column U still highlighted select: conditional formatting; highlight cell rules; more rules; use a formula to determine which cells to format
8 Type: =$U$1="off"
9 Format: set fill to white ('no fill' will not work)
10 Select: conditional formatting; manage rules
11 Highlight the white fill formatting line and put it to the top of the list using the arrows next to the delete rule button

 

[Bub_the_Zombie]

I am missing something here.

This is what I am doing.

Highlight both tables, (table 2 then table 1)
Open up conditional formating, new rule.
Select "Use a formula to determine which cells to format.

type =AND($J$59="On",$U4="Brad") in the box Format values where this formula is true

nothing happens

I am using J59 As the ON toggle

also I am using a laptop without any F keys

 

[Snakehips]

I am missing something here.

This is what I am doing.

Highlight both tables, (table 2 then table 1)
Open up conditional formating, new rule.
Select "Use a formula to determine which cells to format.

type =AND($J$59="On",$U4="Brad") in the box Format values where this formula is true

>>>>

>>>> Click 'Format' >>> Fill Tab >> Select desired colour >> Ok >>Ok
And then try enter Brad in column U

Repeat above for other Names / Rules

 

[Bub_the_Zombie]

@snake, still nothing

@CW/NoIs, that works to highlight the single cell not the row, is there a way to select it without doing the entire column? The headers of the chart have color, when it goes white it fills them in white too.