Conditional formatting based on row data

default_name

Board Regular
Joined
May 16, 2018
Messages
156
Office Version
  1. 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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,178
Office Version
  1. 365
Platform
  1. Windows
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
 

default_name

Board Regular
Joined
May 16, 2018
Messages
156
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
YES and YES!!! :)
You guys are wizards!!
Thank you!!!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,178
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

default_name

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,178
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

default_name

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,178
Office Version
  1. 365
Platform
  1. Windows
Can you please decide exactly what you want, rather than continually "moving the goal posts"
 

default_name

Board Regular
Joined
May 16, 2018
Messages
156
Office Version
  1. 2016
Platform
  1. Windows
  2. MacOS
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,255
Messages
5,769,067
Members
425,515
Latest member
baltusf

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
Top