Conditional Formatting to Highlight Cell when Previous Cell was Identical (Horizontal

dinesti

Board Regular
Joined
Nov 9, 2006
Messages
77
Hi,

There is a similar post that deals with this problem for Vertical cells, but I didn't understand the solution well enough to convert for Horizontal data.

My data is arranged as follows:
1617726604529.png


I want to:
Highlight the blank cells that occur for multiple columns across.
But Ignore blank cells that only occur once horizontally. e.g. R6, or O7

The highlighted cells above are an example of the cells I want to be formatted.



Original post here:
 

Attachments

  • 1617726421840.png
    1617726421840.png
    17.5 KB · Views: 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825
Try:

Book1 (version 1).xlsb
MNOPQRSTUVWXYZAA
711721172561311
81121114346328
921121131114310
1011111359116
111341221
1231321
13113456789
Sheet20
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M7:AA13Expression=(M7="")*(N7="")+(L7="")*(M7="")textNO


Also, please look into installing the XL2BB tool. It makes it so much easier to work with your data, rather than making the helpers have to retype everything. See the link in the reply box or my signature.
 

dinesti

Board Regular
Joined
Nov 9, 2006
Messages
77
Hi Eric,

I will install the xl2bb tool, once I can close all my other worksheets.

This worked, but the formatting is not going all the way to the end and is formatting the last number

I expanded your formula to the rest of my data - maybe I did something wrong (the full width of my data set is A to AC, but column A is my row header so I excluded in the formula)

=(C6="")*(D6="")+(B6="")*(C6="")

1617729562409.png
 

dinesti

Board Regular
Joined
Nov 9, 2006
Messages
77
Lets see if this works

Row Labels1 Day 0307211 Day 0308211 Day 0309211 Day 0310211 Day 0311211 Day 0312211 Day 0313211 Day 0314211 Day 0315211 Day 0316211 Day 0317211 Day 0318211 Day 0319211 Day 0320211 Day 0321211 Day 0322211 Day 0323211 Day 0324211 Day 0325211 Day 0326211 Day 0327211 Day 0328211 Day 0329211 Day 0330211 Day 0331211 Day 0401211 Day 0402211 Day 040321
BLKDIAMD CHEESE COMBO MARBLE 128 G - 682008593057312319222127412310152024416228232010612712018578858225612239
9762 - NBAY TROUT LAKE & CONNAUGHT FRESHCO6311212117211725613117
3869 - SPENCE & BLVD CARTIER FRESHCO122111112111434632839
9803 - HAMT BARTON&GAGE FRESHCO2122112113111431011
3854 - TRAFALGAR & HIGHBURY FRESHCO12122111113591165
3840 - BRANT & UPPER MIDDLE FRESHCO32111151243865
9772 - PRINCESS & ANDERSEN FRESHCO1211122114231911
9879 - EGLINTON & GABIAN WAY FC215432111643215
3881 - BARTON &STRATHEARNE FRESHCO1212112342151617
3853 - FRESHCO MARKET&ICOMM311411433824112
3861 - FRESHCO HWY12 & HWY711121117315265
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,825

ADVERTISEMENT

It should just be a matter of getting the ranges right, both the selected range, and the range in the formula:

Book1 (version 1).xlsb
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1
2
3
4
5
6Row Labels1 Day 0307211 Day 0308211 Day 0309211 Day 0310211 Day 0311211 Day 0312211 Day 0313211 Day 0314211 Day 0315211 Day 0316211 Day 0317211 Day 0318211 Day 0319211 Day 0320211 Day 0321211 Day 0322211 Day 0323211 Day 0324211 Day 0325211 Day 0326211 Day 0327211 Day 0328211 Day 0329211 Day 0330211 Day 0331211 Day 0401211 Day 0402211 Day 040321
7BLKDIAMD CHEESE COMBO MARBLE 128 G - 682008593057312319222127412310152024416228232010612712018578858225612239
89762 - NBAY TROUT LAKE & CONNAUGHT FRESHCO6311212117211725613117
93869 - SPENCE & BLVD CARTIER FRESHCO122111112111434632839
109803 - HAMT BARTON&GAGE FRESHCO2122112113111431011
113854 - TRAFALGAR & HIGHBURY FRESHCO12122111113591165
123840 - BRANT & UPPER MIDDLE FRESHCO32111151243865
139772 - PRINCESS & ANDERSEN FRESHCO1211122114231911
149879 - EGLINTON & GABIAN WAY FC215432111643215
153881 - BARTON &STRATHEARNE FRESHCO1212112342151617
163853 - FRESHCO MARKET&ICOMM311411433824112
173861 - FRESHCO HWY12 & HWY711121117315265
Sheet21
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B7:AC17Expression=(A7="")*(B7="")+(B7="")*(C7="")textNO
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,146
Messages
5,640,392
Members
417,140
Latest member
whiteprose

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