CF not formatting all cells

[Snolune]

I'm using a CF to format cells based on two values and it only formats one cell. It's applied to all cells to be changed and is only filling H7.

Rule: =MATCH(H8,V4,0)

Format: Grey Fill


Applies to: =$H$7:$J$35


Not sure if I'm going about this the hard way or not, but matching Day in row 8 (Sun, Mon, etc) to manually inputted day in cell V4 to format background to grey for range of cells above/below Day on a form that's showing two full weeks.

 

[jtakw]

Hi,

Assuming $H$7:$J$35 is checking corresponding cell in row 8, but V4 is constant, try:

=MATCH(H8,$V$4,0)

Also note that your H8 is within the range $H$7:$J$35, so not sure if that's correct.

 

[Snolune]

Thanks, but it's still only changing one cell. I even applied the CF to H9, I9, J9 and only H9 worked. Scratching my head on this one.

 

[jtakw]

Hi,

Had a second look at your formula, all it's doing is checking if H8=V4, is that right?
Because it can be just =H8=V4

If that's not the case, please explain how your data is laid out, and what you want.

 

[Snolune]

jtakw, that would be correct. The problem is that it isn't applying to all the cells that have been chosen.

 

[Snolune]

jtakw, that would be correct. The problem is that it isn't applying to all the cells that have been chosen.

The following cells are not being filled.
H8 (merged)
I10
I11
H19, I19, J19
H26, I26, J26
H31, I31, J31


Drop Down Menu: V4 [Monday, Tuesday, Wednesday, Thursday, Friday]

Cells formatted with Conditional Formatting
H7 through J35

CF Rule: =H8=V4
Fill H7:J35

 

[Joe4]

Should EVERY cell in your range (H7:J135) be looking at cells H8 and V4?
If so, they you need to lock them down, like this:

=$H$8=$V$4

So if you select your entire range, and then enter that formula, it should work.

Also, I would HIGHLY recommend getting rid of the merged cells. Merged cells cause nothing but trouble in Excel! They interfere with a whole myriad of things, like sorting, VBA, ....
If you are just merging cells across rows, you can get the same visual effect without all the issues by using the "Center Across Selection" formatting option instead.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[Snolune]

Thank you Joe4 that fixed it. Sometimes the simplest solutions are the hardest to see.

 

[Joe4]

You are welcome!

Yes, when applying formulas over a range of cells, you want to use Absolute References ($) in front of the column/row references that you want to lock down.
If you leave them off, they will "float" as you move across/down the cells in your range.

See here for a write-up on absolute, relative, and mixed range references: http://www.excel-easy.com/functions/cell-references.html

 

[Snolune]

Thanks for the links, I'll be doing some reading.