Conditional formatting in a drop down list of merged cells

spr1nger

New Member
Joined
May 18, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
I have 4 merged cells that comprise a dropdown list of companies. The list of companies is coming from sheet 2 which is hidden. I want to highlight the merged cells that shows the company name IF it isn't one specific company.
What I'm running into is that only half of the merged cells are highlighting. I have to select the dropdown twice for all 4 to highlight. Same goes for the opposite. When I select the company I do NOT want highlighted, only half of the highlight goes away, I have to select the company twice. Anyone experience this before? I'm using the following rule:
1664288127519.png
1664288145837.png


Giving me only half on or half off.

1664288193234.png
1664288203853.png
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,740
Office Version
  1. 365
Platform
  1. MacOS
merged cells can be difficult

but , it can work


Conditional Formula.xlsx
ABCDEFG
1
2company namecompany
3company name
4the company
5
6
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E2Expression=B2="Company Name"textNO
Cells with Data Validation
CellAllowCriteria
B2:E2List=$G$2:$G$7



Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
 
Upvote 0

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,531
Office Version
  1. 365
  2. 2010
That is why one should NOT use merged cells...Instead use Format - Format as "Center across selection" is easier to use
 
Upvote 0

spr1nger

New Member
Joined
May 18, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
merged cells can be difficult

but , it can work


Conditional Formula.xlsx
ABCDEFG
1
2company namecompany
3company name
4the company
5
6
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:E2Expression=B2="Company Name"textNO
Cells with Data Validation
CellAllowCriteria
B2:E2List=$G$2:$G$7



Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed
 
Upvote 0

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
6,740
Office Version
  1. 365
Platform
  1. MacOS
you said
I want to highlight the merged cells that shows the company name IF it isn't one specific company.
still not clear based on the share

BUT - i have setup the dropdown on the merged cell in row 11
and then used conditional formatting to highlight if the cell if it is not EpicTech

But i'm sure this is not what you actually want

But the sample below now DOES what you have said
I want to highlight the merged cells that shows the company name IF it isn't one specific company.
so will highlight RED unless the name selected is EpicTech all others are highlighted

 
Upvote 0

spr1nger

New Member
Joined
May 18, 2021
Messages
22
Office Version
  1. 365
Platform
  1. Windows
you said

still not clear based on the share

BUT - i have setup the dropdown on the merged cell in row 11
and then used conditional formatting to highlight if the cell if it is not EpicTech

But i'm sure this is not what you actually want

But the sample below now DOES what you have said

so will highlight RED unless the name selected is EpicTech all others are highlighted

Thank you! I think this will work. I'll just apply it in a different spot. Thanks so much!
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,531
Office Version
  1. 365
Platform
  1. Windows
When marking a post as the solution, please mark the orignal post that contains the solution (not your own reply acknowledging that some other post is the solution).
I have updated it for you.
 
Upvote 0

CanRose1957

New Member
Joined
Oct 10, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
HI @etaf

I have a similar problem, but pertaining to merged rows. For instance, I have merged B5, B6, and B7. I have two concerns:
1) I want to add a drop down list to these merged grouped cells, but the arrow for the drop-down menu appears in the middle, around B5. Is there any way of changing this so it appears at the bottom of B7, like it normally would with unmerged cells?
1665449404035.png

2) I want to add conditional formatting to these merged cells. So, if someone selects, from the dropped down list, such as, "On Track," I would like the entire group of cells (B5 to B7) to turn green. (Currently, only the top two cells fill green).

1665449567238.png


If you could help, that would be AWESOME. I have a share this excel sheet and would really like this to work.

thanks,
Rose
 

Attachments

  • 1665449287766.png
    1665449287766.png
    126.5 KB · Views: 0
  • 1665449324651.png
    1665449324651.png
    86.5 KB · Views: 0
Upvote 0

Forum statistics

Threads
1,186,516
Messages
5,958,300
Members
438,348
Latest member
JS050

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