[ms office - 2013] Conditional Formatting Problem

[GRitchie]

Hello,

I've got a conditional formatting rule across some merged cells, however when I change the drop down, this happens:

 

(http://i.gyazo.com/40c29526461f2e54049c06ac78b5b083.gif)

 

Any ideas why?

[ThomL]

Looks like an excel graphic refresh issue... have you tried expanding the column so that your conditionally formatted cell isn't over lapping the columns to the right of it?

[GRitchie]

Looks like an excel graphic refresh issue... have you tried expanding the column so that your conditionally formatted cell isn't over lapping the columns to the right of it?

 

Yeah, doesn't do it when you extend the column. But I don't want to extend it, due to columns below!

[ThomL]

can you combine the cells? or merge the cells, can't remember the correct terminology. But my thoughts are that this would stop the overlap? Edited March 26, 2015 by ThomL

[GRitchie]

can you combine the cells?

 

In the original they are merged .GIF

[spadam]

I suspect that the conditional formatting is using a relative reference so the first cells is reading the value in the first of the merged cells but the other 2 are reading the cells underneath themselves and not the correct value.

 

The other possibility is that it's the fact that the cells are merged that is causing the issue. I know they can be problematic when manipulating cells via vba. Instead of merging the cells you can center the content horizontally (Select the cells > Home > Alignment > Text Alignment > Horizontal) which achieves the same visual effect but keeps the data in just one of the cells. This may help you to see what is happening with the conditional format.

[LosOjos]

Top of my head, Excel is checking the value in the first cell of the merge (let's call it A1) and applying CondF to that. When it changes, it's not recognising this in the other cell (B1 for example)

 

I think you either need to make sure the Cond F range covers each of the individual cells involved in the merge (A1:B1) or add a formula to a cell nearby to handle the True/False, applying Cond F based on that.

 

For instance, if A1 and B1 are merged, then in C1 you could put =A1="ORDER PLACED"

Then you add the Cond F as a formula, pointing at the control cell i.e. =C1

When C1 is TRUE (i.e. text reads "ORDER PLACED"), this Cond F will be applied, so set the green BG

 

Then add a second Cond F formula, =NOT(C1)

When C1 is FALSE, this Cond F will be applied.

[ThomL]

any chance of getting a copy of the sheet?

[GRitchie]

I suspect that the conditional formatting is using a relative reference so the first cells is reading the value in the first of the merged cells but the other 2 are reading the cells underneath themselves and not the correct value.

 

The other possibility is that it's the fact that the cells are merged that is causing the issue. I know they can be problematic when manipulating cells via vba. Instead of merging the cells you can center the content horizontally (Select the cells > Home > Alignment > Text Alignment > Horizontal) which achieves the same visual effect but keeps the data in just one of the cells. This may help you to see what is happening with the conditional format.

 

I may be doing this wrong, but it doesn't then spread across all the cells like I want?

[Steve21]

It's just a bug, any cells after second that are merged will always be one behind it. If you have 100 cells merged, 1-2 will be right coloured, 3-100 wrong

 

Either change it to 2 cells merged, or use conditional formatting per cell rather than merging one.

 

Steve

[GRitchie]

It's just a bug, any cells after second that are merged will always be one behind it. If you have 100 cells merged, 1-2 will be right coloured, 3-100 wrong

 

Either change it to 2 cells merged, or use conditional formatting per cell rather than merging one.

 

Steve

 

So there's no fix?

[Steve21]

So there's no fix?

 

Not that I've found yet, but tbh not been looking majorly, as normally just use a word around.

 

Do you specifically need that to be 3 cells merged? You can always have the right two merged, and then a conditional if on the left that changes based on 2's value.

 

Steve

[GRitchie]

Not that I've found yet, but tbh not been looking majorly, as normally just use a word around.

 

Do you specifically need that to be 3 cells merged? You can always have the right two merged, and then a conditional if on the left that changes based on 2's value.

 

Steve

 

Just makes it look nicer and fits the text in, just merging two doesn't work quite as well :/

[ThomL]

I've just tested this in 2013, if I merge the cells to the right of the conditional cell together first and then merge this with the conditional cell i works as you wish. Have you tried this?

 

*edit*

 

Seems to work either way with 2013. :/

Edited March 26, 2015 by ThomL

[spadam]

Here is an example using the conditionally formatted cells centered horizontally across the selection rather than using merged cells if that helps at all

 

format.xlsx

 

Edit: I've just checked and it also works fine for me if the cells are just merged so I can't recreate the issue..

Edited March 26, 2015 by spadam

[GRitchie]

Here is an example using the conditionally formatted cells centered horizontally across the selection rather than using merged cells if that helps at all

 

[ATTACH]29873[/ATTACH]

 

Edit: I've just checked and it also works fine for me if the cells are just merged so I can't recreate the issue..

 

Thank you!

I've just had to change it around a little to make it work, I've attached the sheet. Toner Supply Levels.xlsx

 

 

Does anyone know how to get a date picker working? It's not there in developer tools for me!