HELP: Need a quicker way to compare a range of columns using conditional formatting

Hi, I need a quicker way to compare a range of columns using conditional formatting

I want to compare A2 & A1, B2 & B1, C2 & C1, D2 & D1, E2 & E1.

How I do is one at a time.

Example: Starting with cell B3, I use the rules "=B3<>A3" with the range "=$B$3:$B$12".

Then i will repeat for cell D3, then F3, then H3 then finally J3.

Instead of doing them one at a time, is there a quicker way to do this?

Thanks

I have attached a file for your reference. I did cell B3 as a sample.

Thanks

Comparison table.xlsx

Hi nez329

You can cut and paste the FORMATS.

Select a range of cells having the proper conditional format (e.g. B3:B12)
Press CTRL-C to copy
Select the first cell in the destination range (e.g. D3)
Edit > Paste Special > Formats > OK

Lewis

Originally Posted by LJMetzger

Hi nez329

You can cut and paste the FORMATS.

Select a range of cells having the proper conditional format (e.g. B3:B12)
Press CTRL-C to copy
Select the first cell in the destination range (e.g. D3)
Edit > Paste Special > Formats > OK

Lewis

Oh, forgot to add that some of the data is strikethrough. So that will not be possible.

Yes, it is possible with a little VBA Macro trickery, unless someone else has some magical knowledge on how to do what you want without VBA. Your workbook can remain an .xlsx file if you desire, because the Macros are a one time only thing.

Here is what you have to do:

a. Save a Backup Copy of your file, in case disaster strikes.

b. Install the macros that follow in an ordinary module in your Excel file. Alternately they can be installed in your PERSONAL.XLSB file (Excel 2010). Installation instructions below the macros.

c. Run Macro SaveStrikeThroughCells() which saves the addresses of all the 'StrikeThrough' cells on the sheet.

d. Cut and paste the Conditional Formats as many times as needed.

e. Run Macro RestoreStrikeThroughCells() to restore the 'StrikeThrough' cells.

f. Save your file if you are satisifed with the results. Save as .xlsx to remove the macros, or if you want to keep the macros save as .xlsm

Lewis

Please Login or Register  to view this content.

------------------
0. To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) (to cut and paste the above macros) see:
http://www.ablebits.com/office-addin...a-macro-excel/

In post #3 OP said:

Oh, forgot to add that some of the data is strikethrough.

I'm at a loss on how to maintain the strikethrough in Excel 2003, after replicating the Conditional Formatting manually. That's why I went to the VBA solution.

Lewis

Originally Posted by FDibbins

Much easier/simpler way....
open the CF window, and in the "applies to" section, click the range drop-down, hold down shift and then just select the other ranges (=$B$3:$B$12,$D$3:$D$12,$F$3:$F$12,$H$3:$H$12,$J$3:$J$12,$I$3:$I$12)

Thanks. SHIFT did not work for me but CTRL did however.

Originally Posted by FDibbins

It was pointed out to me (by Lewis) that "applies to" is not an option in 2003 - excelent catch holding down shift and selecting the different ranges should still work though (as suggested by sktneer)

However, Nez, I notice that the file you uploaded indicates 2007 or later (.xlsx), but your profile says 2003. Please update your profile as necessary, members tailor questions based on your excel version

I am using 2007 at home. Created the workbook at home. But office is using 2003. So I am limited to 2003. Sktneer's 2nd option is most viable. However i do like to ask, will there be any implication between his method & doing it one at a time?

Thanks

Thanks sktneer, your 2nd option is most viable for me ATM.

Originally Posted by LJMetzger

Yes, it is possible with a little VBA Macro trickery, unless someone else has some magical knowledge on how to do what you want without VBA. Your workbook can remain an .xlsx file if you desire, because the Macros are a one time only thing.

Thanks LJMetzger for your effort. Will try this out when i find the courage, as i am not familar with VBA

Originally Posted by FDibbins

when using CF, avoid using entire column or row ranges, CF can quickly become a resource-hog and slow things down.

Once you have the last row identified, you can then (if you use "applies to") manually adjust the ranges

I see what you mean. Thanks

Thank to all the guys, for the help