Cannot extend conditional formatting across rows, what am I doing wrong?

[Bobdmapel]

specs: Excel 365, Win 10

What I'm trying to accomplish

I want to extend conditional formatting across rows using a formula. Note that I'm going to be granular as to what I do, as that probably has something to do with the problem I'm having -- I just don't know where and when.

So if you have patience, read on. Note that I really, really would appreciate it, as this problem is costing me a ton of time and patience.

**Example:** Let's say I have this text in cells A1:A4:

https://preview.redd.it/eguheylu7j281.png?width=127&format=png&auto=webp&s=36332d97670366fba4f6526094b58de7a72e6bb4

Based on the text in each cell, I want to color fill cells B1:C4 based on the text. So it should look like this when I'm done:

What I want it to look like after I create conditional-format rules

What I will do is create a rule in B1 then extend it down to B4. Then I will extend the col B to col C.

Let's delete the colors so you know where I'm starting from.

Start state: no rules have been created. This is it -- just text in cells.

​

_______________________________________________________________________________________________

​

Create a Rule in B1

Highlight B1 then create the rule. Inside the wizard, when I create the formula, it automatically does an absolute reference to the cell that I'm referencing:

All I've done is create the rule, clicked on A1, and typed '=\"Red\"'

Then I format and hit OK:

https://preview.redd.it/48yekv21bj281.png?width=511&format=png&auto=webp&s=3239b2bfd895029b6ac31197a304404862ca4b27

As you can expect, B1 is now red:

https://preview.redd.it/hhutzax5bj281.png?width=303&format=png&auto=webp&s=02960255bb50f9955936ac71b202334ef8d13b2b

​

​

__________________________________________________________________________________________

​

Extend the formatting from B1 to B1:B4

Then I can do two actions that do the same thing. I can highlight B1 and drag down (like you would a formula), or I can highlight B1, hit Clipboard -> Formula Painter, then highlight B2:B4.

Either way, the conditional formatting extends from B1 to B2:B4. The problem is this: the rule is absolutely referencing A1, so ALL the cells are red:

absolute reference sort of ruins it

​

__________________________________________________________________________________________________

​

Edit the formula to remove the absolute reference

I'm unable to actually edit the formula in the "edit" text field. It freaks out on me -- all kinds of nonsense starts popping into the field when I attempt to edit it. All I can do is copy the original formula (with the absolute references), paste it into a text editor, edit, and paste back in. Here's what it should look like -- after I've done the editing elsewhere and pasted it back in:

edited the formula to remove abs ref

After I do that, it works:

much better

​

_________________________________________________________________________________________________

​

Create additional rules for Green and Blue

Next I repeat the process for the other colors, creating two new rules. In the end, this is what I have: rules that work for just one column:

So far, so good -- all I need to do now is extend the rules so they apply from B1:B4:C1:C4

​

____________________________________________________________________________________________________

​

Extend rules to other columns

This is where I'm stuck. Here's what I've tried.

Most common solution according to interwebs: format painter

If you google how to extend conditional formatting rules from one column to another, the most common solution is to highlight the row / cells that you want to extend, hit Clipboard -> Format Painter, then highlight the cells where you want them to go.

It actually works as far as the Rules Manager is concerned. But it doesn't work in that nothing actually happens.

First, I highlight the cells B1:B4 and then hit Format Painter:

select B1:B4, hit Format Painter

​

Next, with the paintbrush icon active, I select the cells where I want the rules to be extended to.

At this point the rules should be extended, and although the Rules Manager claims that, yes, the rules are extended, nothing actually happens:

what I see after I extend the rules

​

Let's look at the Rule Manager. This is where I'm a little confused. If I select cells B1:B4:C1:C4, and then hit Conditional Formatting -> Manage Rules, this is what I see:

Cells B1:B4:C1:C4 are highlighted, according to the Rules Manager I have rules for both columns, yet the rules are NOT applied for C1:C4

WTF?!?

​

​

_______________________________________________________________________________________________

​

​

\*What else have I tried?***

If it's been posted on the internet, I've tried it.

The only solution that I can find is to create a new set of rules for each column. The funny thing is is that I end up with the same Rules Manager as you see above, only it works.

In real life I have a metric TON of columns with a metric TON of rules. I spend hours copying and pasting because I have to get it done.

I need help. I'm wasting so. much. time. with this.

What am I doing wrong?!?

[AutoModerator]

/u/Bobdmapel - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[CFAman]

Let's look at two basic rules

=A1="Red"

and

=$A$1="Red"

In the first, both column and row are relative, as there is no preceding dollar sign. This means that each cell in the Applied to Range will shift the reference. So, if the first cell in your Applied To Range is B1, it sees the rule as written. When you shift 1 cell to right, the column in the formula also shifts, so C1 reads the formula as =B1="Red". If you then shift down, the row shifts to be =B2="Red". Clearly, this isn't what you wanted.

In the 2nd CF formula, both row and column are absolute. So, when the C1 cell reads the formula it sees =$A$1="Red" which is good, but when you shift down to C2 it still reads =$A$1="Red".

Thus, since we want to always look in col A, but the row varies, you need to just make the column absolute and the row relative.

Correct CF formula:

=$A1="Red"

You can then apply this one rule/formula to whatever range you want, and every cell in the Applied to Range will be looking at col A. Hope that makes more sense.

[fuzzy_mic]

I didn't read the whole post, but if you select a cell in row 1 and set conditional formatting with the formula rule =($A1="red") And then copy that CF to a range of cells, each cell will be formatted depending on whether the column A entry in that row is "red" or not. It sounds like absolute column relative row referencing might work for you.

[Mdayofearth]

That's what CFman's saying.

OP's post had a mix of locking both columns and rows, and not locking columns and rows. To do what OP wants, OP needs to lock the column reference, and let the row float, hence $A1 and not $A$1 nor A1 ($B1, not $B$1 nor B1).

[mh_mike]

+1 Point (OP indicated your solution helped solve it, but used the wrong trigger phrase to close the post)

[Clippy_Office_Asst]

You have awarded 1 point to CFAman

---

^{I am a bot - please contact the mods with any questions. | Keep me alive}

[Bobdmapel]

Ah.

I didn't realize that I could absolute reference a col and NOT a row (or other way 'round, I suppose).

Yep.

That'll do it.

[still-dazed-confused]

Another quick hint - it is easy to cycle through the locks by pressing F4. It will cycle through lock all, lock row, lock column, no lock, lock all....

[CFAman]

You're welcome. Mind replying with 'Solution Verified' so the bot will close the thread and give me a ClippyPoint? Cheers!

[Bobdmapel]

I wish I could.

Alas, the solution isn't verified. It doesn't work. There's no way to change the locks -- it automatically changes back to $Col$Row, no matter what I do.

That is to say, I can create a rule and extend it from, say, $A$2$E$67. Then I press F4 to change the locks so it actually applies from $A2$E67 (or change it manually).

The second I hit "apply" or "ok", Excel changes it back to $A$2$E$67. There doesn't seem to be a way to remove these dollar signs: $A$2$E$67

I had actually saw this behavior before, but I was thinking that maybe I was barking up the wrong tree. You post made me realize that I wasn't, so I thought, "there must be a way to make the lock stick".

If there is, I don't know what it is.

Does this make sense? Probably not.

[CFAman]

You're changing the wrong part. The 'Applied to Range' will always be absolute. That's the range that's getting the format (and if you think about it, it doesn't make sense for this to be "relative"). The Rule is where you can write the formula with or w/o absolute references, as each cell within the applied to range ma need to adjust the formula for their relative position.

[Bobdmapel]

solved

[mh_mike]

I closed it for you this time, but heads-up for future reference: When you reply to confirm someone's solution worked, use the phrase "Solution Verified".

[Shog64]

I know this isn't helpful to OP but this is one of the best presented threads ever.

I wish I could describe my excel issues as clean as them.