Conditional Formatting Across Rows

[Maxymous]

I'm trying to create a sheet that will highlight the highest & lowest value in a row. I have 100 rows with 40 columns. Every odd column is to contain a monetary value and every even column is to contain a rank of the monetary value with the lowest being rank 1 and the highest being rank 20.

I would like a conditional format that will highlight the highest value (red) and the lowest value (green) in the odd columns of each row.
(e.g. highlight the highest value per row, ignoring my rank columns inbetween)

I can get it to do this by selecting each cell on each row along each odd column and setting a top or bottom 1 Conditional Format. I just do want to have to set this 100 times (a total of 200 Conditional formats).

Does anyone know of a way to either copy the conditional formats so that they repeat by rows (it will repeat by columns if I try the copy + paste formatting function) or is there a one size fits all approach for the range? (e.g. two conditional formats in total that will highlight the highest & lowest values per row ignoring the rank formula columns)

Untitled.png

[Ysball]

Hi Maxymous,

if you have the right conditional format rule, copy the cell and paste special (format) it to the entire column.
next step is to copy the fist column with conditional format and the second column with no format.
than paste special (format) this to the entire range and you're done.
excel automatically paste you copied data side to side until the end of the range.

good luck!

[dilipandey]

Hi Maxymous,

Please upload a sample workbook.. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[Kevin UK]

Hi Maxymous

I have using A1:D20 so you will have to change for your worksheets.

Try New Rule

1/ For the MAX: =MAX($A1:$D1)=A1

2/ For the MIN: =MIN($A1:$D1)=A1

[Maxymous]

Dilipandey - I have no idea how to upload the worksheet, I don't appear to have the option to insert an attachment in this reply or when I attempt to edit my original post.

I've got the worksheet doing what I wanted now but it wasn't quite as easy as Ysball suggested.

You can't simply just select the row that you have applied the conditional formatting to and then paste the formatting over the entire range that you want the CF applied to. This will modify the 'Applies To' section to apply by columns (e.g. C4:C103, E4:E103 instead of rows C4, E4, G4 then C5, E5, G5)

I just applied the CF to the required cells in the first rows (even columns) then copied & pasted the formatting for each row (100 rows) this didn't take too long but now the sheet has 200 CF's.

I'm not sure if anyone would be able to provide a shorter way of getting the Conditional Formats.

[FDibbins]

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Ysball]

Hi Maxymous

luckily it is as easy as i say
in the attached file you can copy the first 2 columns and paste it over the rest and all the biggest numbers in the column gets an specific color and the lowest numbers too.

you'll need to change the conditional format in cell a2 to include more rows.
after you've done that you can copy that cell and paste (format) it over the other cells in that column, next step is to copy the columns A and B and paste (format) it over the other columns.

good luck!

[Ysball]

As i highlight the biggest value in the row instead of the highest rank you'll have a difference.
this one is easily solved by changing the column in the format rule:

=A2=MIN(A$2:A$4) needs to be =A2=MIN(B$2:B$4) and
=A2=Max(A$2:A$4) needs to be =A2=Max(B$2:B$4)

good luck!