Conditional formatting based on row data

default_name

Board Regular
Joined
May 16, 2018
Messages
170
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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?

ABCDEFGHI
1NameStep 1Step 2Step 3Step 4Step 5Step 6Step 7Step 8
2Jimmy
[GRAY]​
[GRAY]
* [BLUE]​
[BLUE][BLUE][BLUE][BLUE][BLUE]
3Steven
* [BLUE]​
[BLUE][BLUE][BLUE][BLUE][BLUE][BLUE][BLUE]
4Nadia[GRAY][GRAY][GRAY][GRAY]
* [BLUE]​
[BLUE][BLUE][BLUE]
5Bobby[GRAY][GRAY][GRAY][GRAY][GRAY][GRAY]* [BLUE][BLUE]

6
William[GRAY][GRAY][GRAY]
* [BLUE]​
[BLUE][BLUE][BLUE][BLUE]
7Lindsey[GRAY]
* [BLUE]​
[BLUE][BLUE][BLUE][BLUE][BLUE][BLUE]
8Marcus[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
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Is this what you want? Used green instead of grey for visibility.
Book2
ABCDEFGHI
1NameStep 1Step 2Step 3Step 4Step 5Step 6Step 7Step 8
2Jimmy*
3Steven*
4Nadia*
5Bobby*
6William*
7Lindsey*
8Marcus*
Sheet9
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:I8Expression=COUNTIF($B2:B2,"~*")=0textNO
 
Upvote 0
How about

+Fluff New.xlsm
ABCDEFGHI
1NameStep 1Step 2Step 3Step 4Step 5Step 6Step 7Step 8
2Jimmy[GRAY][GRAY]* [BLUE][BLUE][BLUE][BLUE][BLUE][BLUE]
3Steven* [BLUE][BLUE][BLUE][BLUE][BLUE][BLUE][BLUE][BLUE]
4Nadia[GRAY][GRAY][GRAY][GRAY]* [BLUE][BLUE][BLUE][BLUE]
5Bobby[GRAY][GRAY][GRAY][GRAY][GRAY][GRAY]* [BLUE][BLUE]
6William[GRAY][GRAY][GRAY]* [BLUE][BLUE][BLUE][BLUE][BLUE]
7Lindsey[GRAY]* [BLUE][BLUE][BLUE][BLUE][BLUE][BLUE][BLUE]
8Marcus[GRAY][GRAY][GRAY][GRAY][GRAY][GRAY][GRAY]* [BLUE]
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:I8Expression=COLUMN(B2)>=MATCH("~**",$B2:$I2,0)+1textNO
B2:I8Expression=COLUMN(B2)<MATCH("~**",$B2:$I2,0)+1textNO
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
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
ABCDEFGHI
1NameStep 1Step 2Step 3Step 4Step 5Step 6Step 7Step 8
2Jimmy*
3Steventext
4Nadia1/15/2020
5Bobby*
6Williamout of supplies
7Lindsey*
8Marcus
Sheet2
 
Last edited:
Upvote 0
Just get rid of the ~* inside the match function to leave "*"
 
Upvote 0
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
ABCDEFGHI
1NameStep 1Step 2Step 3Step 4Step 5Step 6Step 7Step 8
2Jimmy12/20/19*
3StevenHasn't started
4Nadia2/15/2020
5Bobby3/10/2020*
6Williamout of supplies
7Lindsey*
8Marcus1/04/20204/21/2020*
Sheet2
 
Upvote 0
Can you please decide exactly what you want, rather than continually "moving the goal posts"
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top