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

 

[Fluff]

So what exactly are the criteria, as there doesn't seem to be anything consistent.

 

[default_name]

So what exactly are the criteria, as there doesn't seem to be anything consistent.

Yeah the data inconsistency is why I brought this back to you guys.
The criteria is a non-blank cell. Sometimes there is a *, other times there is text or a note, and other times there is a date. So I guess it'd just be searching for the first non-blank cell in the range from the right (or the first cell from the right that has some sort of content in it).
Hope that makes sense.

 

[jasonb75]

As long as the empty cells are empty and not blank (there is a difference).

Book1
	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					03/10/2020		*
6	William				out of supplies
7	Lindsey		*
8	Marcus		01/04/2020		4/21/2020	*
Sheet9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:I8	Expression	=COUNTA($B2:B2)=COUNTA($B2:$I2)	text	NO

 

[default_name]

You guys are the best!
Thank you Jasonb75! It works perfectly!
Again, thank you for your patience and my apologies for dragging this out.
I will try to be more careful to include everything at the beginning next time.

 

[Fluff]

Another option

+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					03/10/2020		*
6	William				out of supplies
7	Lindsey		*
8	Marcus		########		4/21/2020	*
Costs

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:I8	Expression	=COLUMN(B2)>=LOOKUP(2,1/($B2:$I2<>""),COLUMN($B:$I))	text	NO

 

[default_name]

Thanks Fluff!
You're awesome!

 

[Fluff]

Glad we could help & thanks for the feedback.