Compare 2 columns and highlight 3rd column

[ilasubbu]

Hi,

I have the requirement of comparing 2 columns and highlighting 3rd column with the appropriate colour. I am using MS Excel 2003 version

E.g
Data set
A1 - '01-Nov-2009'
B1 - '10-Nov-2009'

Action required
Compare A1 & B1. If A1 is less than B1, set "Green colour" background in C1 column.

Should be easy with conditional formatting. But I have to do it for various rows in a single sheet and repeat this with multiple sheets. So, am thinking it's ideal way to write a macro to perform this operation row by row in all sheets. If it's feasible, I can assign that macro to a simple button and make the work easier instead of setting conditional formatting in all the sheets.

 

[PawPawGary]

See if this works for you:

    Range("C:C").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R[0]C[-2]<R[0]C[-1]"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C1").Select

 

[PawPawGary]

Let's try this again..

    Range("C:C").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=R[0]C[-2] < R[0]C[-1]"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("C1").Select

 

[Peter_SSs]

OK, so if you do not want to use Conditional Formatting, you could try this on a copy of your workbook.

Assumptions I've used:

1. This action is to be performed on all sheets in the workbook.

2. Data actually starts in row 2 of each sheet (assuming headings in row 1)

3. Column A can be used to determine the bottom of the data range.

4. No checking is required to see if both cells in column A and B contain data.

If any of the assumptions are wrong or the code doesn't do what you want and you need more help to modify it, post back with more details.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ColourCells()<br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>        <SPAN style="color:#00007F">With</SPAN> ws<br>            .Columns("A").Insert<br>            <SPAN style="color:#00007F">With</SPAN> .Range("A2:A" & .Range("B" & .Rows.Count).End(xlUp).Row)<br>                .FormulaR1C1 = "=IF(RC[1]< RC[2],1,"""")"<br>                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>                .SpecialCells(xlCellTypeFormulas, xlNumbers) _<br>                    .Offset(, 3).Interior.ColorIndex = 4<br>                <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            .Columns("A").Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> ws<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ilasubbu]

Hi Peter,

Sorry for my late response.

It worked well and saved my loads of efforts everyday.

Thanks for your help.

Regards
IK.