Trying to copy Conditional formatting across columns

[FrumpyJones]

I have the following Conditional Format:

Works fine. Problem is I have columns going out to AB (Actually this will go well beyond AB, but for now, AB) that I want to apply this same thing to (above average from 2-15, each individual column). I tried format painter, both on a single cell (B2), and on the entire B column (B2-B15), but they both gave me the following (which gave inconsistent results) after I dragged it to the end. (Start C2, end AB15).

I'd like to avoid having to do every column individually for obvious reasons, but I'm stumped on how to copy the conditional formatting (Which I thought would work) or write a formula that could be copied across the range (Or just a single rule that does it all).

I have had success with making an additional cell (B17) that does the average formula of b2-15, and then do a conditional formatting for >B17, and I can copy THAT all the way across, but it's adding data that I would think I shouldn't need.. but maybe I do?

Thanks in advance

 

[Burrgogi]

Why can't you just edit the current rule you have there? From your screenshot, it shows
=$B$2:$B$15

Just edit that part to:
=$B$2:$AB$15

 

[FrumpyJones]

Why can't you just edit the current rule you have there? From your screenshot, it shows
=$B$2:$B$15

Just edit that part to:
=$B$2:$AB$15

Well, because it's giving me the same inconsistent results. Here's a screenshot (sorry, this is a work machine and will not let me install that cool xl2bb code thinghy) of just three columns and the conditional formatting I'm using for them (And I added the average for each column to highlight the issue:

So, in column B, anything ABOVE 3.13 should get highlighted. Working fine.
In Column C, anything ABOVE 4 should highlight, so the first 4 is questionable, but acceptable.
In column D, however, anything ABOVE 2.75 should highlight, but that 3 in the first cell definitely is not getting highlighted.

Even with the workaround I mentioned in my OP (using cell b17) I now realize it's doing the same thing and I'm now just realizing it's ONLY taking the average from the first column... which is why i get the results I've noted above (4 is > 3.13 and 3 is < 3.13).

So, is there a way to do the do a copy the conditional formating, but for each range have it also move the column that it's checking against? So I guess not the absolute $B$17, but B$17 and then copying it would make the next section C$17, and so on...). Or is it a column-at-a-time endeavor I just have to suck up and do?

 

[Burrgogi]

So, in column B, anything ABOVE 3.13 should get highlighted. Working fine.
In Column C, anything ABOVE 4 should highlight, so the first 4 is questionable, but acceptable.
In column D, however, anything ABOVE 2.75 should highlight, but that 3 in the first cell definitely is not getting highlighted.

As you're talking about the various rules you have, maybe you should take a screenshot (or just copy & paste) the actual rules or formulas you've created.

 

[FrumpyJones]

As you're talking about the various rules you have, maybe you should take a screenshot (or just copy & paste) the actual rules or formulas you've created.

But that's it. I've shown you everything.

 

[Burrgogi]

So as I understand it, you have these 3 rules set up currently.

Column B: anything ABOVE 3.13 should get highlighted.
Column C: anything ABOVE 4 should highlight.
Column D: anything ABOVE 2.75 should highlight.

Are you saying these rules should repeat itself every 3 columns?

Column E: anything ABOVE 3.13 should get highlighted.
Column F: anything ABOVE 4 should highlight.
Column G: anything ABOVE 2.75 should highlight.

If that is indeed what you're trying to do, then yes I don't see any other way but to manually copy & paste column B conditional formatting over column E, H, K, so on & so forth.
Same thing with col. C and D.

EDIT:

you can also hold down the Ctrl key and select your desired columns before pasting. See example:

 

[FrumpyJones]

So as I understand it, you have these 3 rules set up currently.

I apologize. I am not explaining this clearly. Lemme try again.

1. Column B has 14 cells I am working with (2-15). I set up a conditional formatting ABOVE AVERAGE rule to highlight any cell in that range that has a number ABOVE the average of the sum of that range. This worked.
2. I copied the conditional formatting (Using FORMAT PAINTER) and dragged across columns C and D (same ranges (2-15) but they have different values, hence different sums, hence different averages). This did NOT work. Turns out it just kept looking at column B average, and highlighting cells based off of that, instead of the column C (for C2-C15) and Column D (for D2-D15).
3. I then cleared all conditional formatting. Started all over again with only column B. added an =AVERAGE cell to cell B17 and then Set up conditional formatting for the B2-B15 range where if any cell was greater than B17. This worked.
4. I copied B17's formula to C17 and D17. This worked.
5. I copied the conditional formatting same as in step 2. This did NOT work. This caused the same issue.

So, in reality, I have 26 columns right now (All with potentially different values within the same ranges), and endless columns going forward. If do each column individually, it will work (But break my soul).

Is there a way to copy the conditional formatting that will work for each column individually, or is there a conditional format formula that can be written that will do what I need

 

[Fluff]

How about use a formula & use

=B2>AVERAGE(B$2:B$15)

 

[FrumpyJones]

How about use a formula & use

=B2>AVERAGE(B$2:B$15)

Would I highlight all of the columns first, and use that formula, or use that formula for just colum B and then FORMAT paint across the other columns?

 

[Fluff]

Select the entire range & then create the rule using that formula