Conditional Formatting Across Multiple Columns with Multiple Value Conditions

myactiondesign · Feb 15, 2014

Hello,

I need to copy a set of conditions for highlighting cells across several columns whereby the cell values changes as well.

See below an example:

My conditions are:

- If equal to max value highlight green
- If equal to min value highlight red

Now I need to copy across several columns where not only do the cells that have the formatting applied change, but also the values of which are max and min.

Looking for help with the above problem.

Thanks in advance.

!&...

ajmuhs · Feb 15, 2014

myactiondesign said:
Hello,

I need to copy a set of conditions for highlighting cells across several columns whereby the cell values changes as well.

See below an example:

My conditions are:

- If equal to max value highlight green
- If equal to min value highlight red

Now I need to copy across several columns where not only do the cells that have the formatting applied change, but also the values of which are max and min.

Looking for help with the above problem.

Thanks in advance.

!&...

Hello myactiondesign,

You might try to apply a rule to cells F8:F37 where the cell value is equal to F$4 format green. You would then need a second rule for cells F8:F37 where the cell value is equal to F$5 format red.

When you are setting up the rules, highlight cells F8:F37 then hit conditional formatting. The rule should read that the Cell is Equal to F$4. Then set your colors. The set up is the same for the Min value substituting F$4 for F$5.

The only drawback is that you will need to do this for all of the columns separately. If you only had one Max and One Min for the whole sheet, it would be different.

myactiondesign · Feb 15, 2014

ajmuhs said:
Hello myactiondesign,

You might try to apply a rule to cells F8:F37 where the cell value is equal to F$4 format green. You would then need a second rule for cells F8:F37 where the cell value is equal to F$5 format red.

When you are setting up the rules, highlight cells F8:F37 then hit conditional formatting. The rule should read that the Cell is Equal to F$4. Then set your colors. The set up is the same for the Min value substituting F$4 for F$5.

The only drawback is that you will need to do this for all of the columns separately. If you only had one Max and One Min for the whole sheet, it would be different.

Hi ajmuhs,

Thanks for your reply.

I was wondering if it were at all possible to use an OR function within the equal to cell F$4 value, and do it across the whole range and do something along the lines of: =F$4 OR G$4 OR H$4 etc?

ajmuhs · Feb 15, 2014

myactiondesign said:
Hi ajmuhs,

Thanks for your reply.

I was wondering if it were at all possible to use an OR function within the equal to cell F$4 value, and do it across the whole range and do something along the lines of: =F$4 OR G$4 OR H$4 etc?

As far as I can tell that is not possible without a really intense formula if at all. Some of the MVPs on the site may know however, I do not.

still learning · Feb 15, 2014

Hi

If I understand it, you want to show the highest values of each row in the 4 line and the lowest values of each row in the 5 line
Then you want line 4 to be green and line 5 to be red
Then you want to highlight the same value below.

I would put the formula =max(F8:F52) in F4 and format the cell the way you want
Then put the formula =min(F8:F52) in F5 and format the cell the way you want
This will find the highest and lowest number in each row.

Then copy the formulas to T

That takes care of finding the highest and lowest values.

Now to conditional format the data.
Highlight from F8 to F52, then click on the home tab and select conditional format. Pick the option that says : HighLight Cells Rules---equal to. A box comes up to select what to equal to.
Click on F4, then enter, then format the cell using the options provided or click “custom format.. “. Then OK.

Highlight from F8 to F52, again, then click on the home tab and select conditional format. This time pick “new rule”. Pick “Use formula…..” a box comes up with a space to put in the formula.
Put in =F5 then format as before and hit apply if it comes up and ok.

That will highlight any value that matches F4 and F5.
You can try to copy the conditional format using the copy/paste/special or the format painter.
If it doesn’t copy the conditional format, then you have to do each row

Mike

Conditional Formatting Across Multiple Columns with Multiple Value Conditions

myactiondesign

New Member

Excel Facts

ajmuhs

Board Regular

myactiondesign

New Member

ajmuhs

Board Regular

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

Conditional Formatting Across Multiple Columns with Multiple Value Conditions

myactiondesign

New Member

Excel Facts

ajmuhs

Board Regular

myactiondesign

New Member

ajmuhs

Board Regular

still learning

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock