Conditional formatting based on row data

[default_name]

Hey all,

I am not sure if this is even possible, but I figured I'd ask/give it a shot.

In a spreadsheet I have a marker (denoted in cells with a * symbol).
Is there a way (without using VBA) to use conditional formatting to adjust the colors of the cells around the marker?

The following example tracks where certain people are at in a list of steps. The * symbol shows where they are. (ie: Bobby is on Step 7).
Is there a way to use Conditional Formatting to change the color of the cells to the left of the * (stopping with column B) to gray, and to change the cells containing the marker as well as cells to the right to blue?

	A	B	C	D	E	F	G	H	I
1	Name	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8
2	Jimmy	[GRAY]​	[GRAY]	* [BLUE]​	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]
3	Steven	* [BLUE]​	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]
4	Nadia	[GRAY]	[GRAY]	[GRAY]	[GRAY]	* [BLUE]​	[BLUE]	[BLUE]	[BLUE]
5	Bobby	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	* [BLUE]	[BLUE]
6	William	[GRAY]	[GRAY]	[GRAY]	* [BLUE]​	[BLUE]	[BLUE]	[BLUE]	[BLUE]
7	Lindsey	[GRAY]	* [BLUE]​	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]
8	Marcus	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	* [BLUE]​

Hope that makes sense. I wanted to try to illustrate it in the above table, but I did not see any color options.

Again, I am not sure if this is even possible without VBA, but if it is, this would be great!
Thanks

 

[jasonb75]

Is this what you want? Used green instead of grey for visibility.

Book2
	A	B	C	D	E	F	G	H	I
1	Name	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8
2	Jimmy			*
3	Steven	*
4	Nadia					*
5	Bobby							*
6	William				*
7	Lindsey		*
8	Marcus								*
Sheet9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:I8	Expression	=COUNTIF($B2:B2,"~*")=0	text	NO

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8
2	Jimmy	[GRAY]	[GRAY]	* [BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]
3	Steven	* [BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]
4	Nadia	[GRAY]	[GRAY]	[GRAY]	[GRAY]	* [BLUE]	[BLUE]	[BLUE]	[BLUE]
5	Bobby	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	* [BLUE]	[BLUE]
6	William	[GRAY]	[GRAY]	[GRAY]	* [BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]
7	Lindsey	[GRAY]	* [BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]	[BLUE]
8	Marcus	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	[GRAY]	* [BLUE]
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:I8	Expression	=COLUMN(B2)>=MATCH("~**",$B2:$I2,0)+1	text	NO
B2:I8	Expression	=COLUMN(B2)<MATCH("~**",$B2:$I2,0)+1	text	NO

 

[default_name]

YES and YES!!!
You guys are wizards!!
Thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[default_name]

Is there a way to have this work with any text or identifier (not just a * symbol).
I recall using something similar that would start looking from the right until it bumped into something (a LEFT function?)
Could this be used here as well?

Using your example, Fluff, for visuals.
Basically starting at Column I and looking to the left until it finds something. Then coloring all of the blank cells it went through as well as the cell with text, blue (with the text to the left of it, up until column B, being colored gray).

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8
2	Jimmy			*
3	Steven	text
4	Nadia					1/15/2020
5	Bobby							*
6	William				out of supplies
7	Lindsey		*
8	Marcus
Sheet2

 

[Fluff]

Just get rid of the ~* inside the match function to leave "*"

 

[default_name]

Thanks Fluff!
You are really helping me get this where it needs to be...I keep discovering little details that are making this more complex.

Removing the ~* helped, but it is still looking for the first item from the left...there are a few cells in my actual data that have some information in previous cells.
Is there a way for this to work from the right side?
Like this:

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8
2	Jimmy		12/20/19	*
3	Steven	Hasn't started
4	Nadia					2/15/2020
5	Bobby					3/10/2020		*
6	William				out of supplies
7	Lindsey		*
8	Marcus		1/04/2020		4/21/2020	*
Sheet2

 

[Fluff]

Can you please decide exactly what you want, rather than continually "moving the goal posts"

 

[default_name]

Can you please decide exactly what you want, rather than continually "moving the goal posts"

My apologies.
When I made my original post I had to fabricate data (I cannot use my actual data here). It was only afterwards that I realized the differences.
Post #8 is the final goal post here.
Again, my apologies.
I really appreciate you guys and your patience! You have been such a blessing!