CONDITIONAL FORMATTING AFTER EVERY CHANGE (across all rows)

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Hello Guru's, I'm stumped. My conditional kind of works but not all the time. Please help.

I have a small set of data with 3 columns on it. I need the conditional formatting to go across all the 3 columns based on a conditional formula in Column B.
I need the conditional formatting to be in column starting in cell B2.

The first row will always be green across all three columns. Green will be the initial color formatting.
Blue at the 2nd job number change - then back to green at the 3rd change in the job number. Back and forth like that for the length of the data.

1) If the job number in consequent rows are the same, then that cell should be green as well (column A and C of that row should be green as well). Could be the same for several rows. Same color then.
2) At the next job number change, the color for that cell should be blue (columns A and C of that row should be blue as well)….

I will attach a clean set of data and a set of data showing the correct color so you can see what the results should be. I'm doing this in case I'm not clear for you.

Column A: employee name
Column B: job number
Column C: date worked on job

Specifics:
Column C: The date order is the first sort. It is from older to newest date. It has to be that way.
Column B: This job number are sorted so that all the "like" job numbers are together from smaller to bigger


Thank you so much!
 

Attachments

  • Capture_raw data set_no color coding.PNG
    Capture_raw data set_no color coding.PNG
    18.6 KB · Views: 4
  • Capture_raw data set_WITH color coding added.PNG
    Capture_raw data set_WITH color coding added.PNG
    19.4 KB · Views: 4

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
This works with a quick test,
Excel Formula:
=NOT(MOD(ROWS(UNIQUE($B$2:$B2)),2))
 

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
I'll try it right now. Give me a sec to try.
 

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Okay, I tried it. It work only in column B. Can you adjust the formula so that the green color highlights in column A and C as well.

Also, I don't see how this formula Blue.
The first block of data should be green - then blue at the next job number change. I think you are sooooo close
 

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello Jason, I wanted to add that the first color will always be green - whatever color cell b2 is should be extended to cell A2 and C2.

The color should change at the next job number change.

Example: If there are 5 rows with the same job number - then the color in column A and C or those exact rows should be the same color.

The color coding will be Green, Blue, Green, Blue, etc. etc...…Thank you!!!! I added a snapshot for you.
 

Attachments

  • Capture_raw data set_WITH color coding added_same color is same job number.PNG
    Capture_raw data set_WITH color coding added_same color is same job number.PNG
    25.1 KB · Views: 1

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
It work only in column B. Can you adjust the formula so that the green color highlights in column A and C as well.
It works with any column that you apply it to.
Also, I don't see how this formula Blue.
The first block of data should be green
In your first post, you said
Green will be the initial color formatting.
which implies that the green will be the default colour (manually filled to all cells without conditional formatting).

The formula that I provided for conditional formatting will change the default green to blue for the 2nd, 4th, 6th, etc job numbers.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,609
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

I have taken
Hope jasonb75 does not mind, apologies if you do
I had been looking at a few different solutions , but jasonb75 is the best , as you have a version that supports
and then tested if 0 and 1 to choose green or blue
slight change

jasonb75

Formula , and so zero = green and 1 = blue


BAND Groups COND-FRMT.xlsx
ABC
21
31
41
52
63
74
85a
95a
106a
116a
126a
137
147
157
167
1712
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:C20Expression=AND($B2<>"",MOD(ROWS(UNIQUE($B$2:$B2)),2)=0)textNO
A2:C20Expression=AND($B2<>"",MOD(ROWS(UNIQUE($B$2:$B2)),2)=1)textNO
 
Solution

JuicyMusic

Board Regular
Joined
Jun 13, 2020
Messages
94
Office Version
  1. 365
Platform
  1. Windows
Thank you to Jason....and to you Etaf. I'm trying this right now. Be back.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,497
Office Version
  1. 365
Platform
  1. Windows
Hope jasonb75 does not mind, apologies if you do
Not at all, Wayne! Saves me having to set up an example again :)

I keep forgetting to save my test files before closing the lid on my laptop and then losing them when it decides to crash (frequently).
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,609
Office Version
  1. 365
Platform
  1. MacOS
I keep forgetting to save my test files before closing the lid on my laptop and then losing them when it decides to crash (frequently).
Thanks

I do the same, not crashing, but I delete the spreadsheets and don't save - at least i can get them back copying the XL2BB entry , so not all lost
 

Watch MrExcel Video

Forum statistics

Threads
1,128,165
Messages
5,629,068
Members
416,363
Latest member
zaveedd

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