condition format to check for differences

[mdoherty83]

For Each Cell In Range("New")</SPAN></SPAN>
Cell.Select</SPAN></SPAN>
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=RC[1]"</SPAN></SPAN>
     With Selection.Interior</SPAN></SPAN>
        .ColorIndex = 42</SPAN></SPAN>
        .Pattern = xlSolid</SPAN></SPAN>
    End With</SPAN></SPAN>
 
Next</SPAN></SPAN>

Hi there, I'm trying to get VBA to compare all cells in a 1column range of cells (Range = "New") to the cell to the right, and if there are any differences format the cell with color. What its doing at the minute is colouring all the cells. I'm in the very early stages of learning VBA and am basically picking this up as I go along, so please assume i know nothing. My initial guess is that it has something to do with niether of the cells being numbers?? They both contain text along the lines of "C3, C3a, C4 . . . etc etc".

I've put all the code in form the button below for the sake of context. Please note that the column i wish to apply the conditional format to does not exsist before the marco is run, so I don't have the oppertunity to do this outside of VBA before the marco is run.

Any help for this poor newbie is appreciated.

Private Sub CommandButton2_Click()</SPAN></SPAN>
Dim Cell As Range</SPAN></SPAN>
Range("e:e").Insert Shift:=xlToLeft</SPAN></SPAN>
Range("e7:e100").FormulaR1C1 = "=IF(RC[-3]="""","""",(IF(ISNA(VLOOKUP(RC[-3],'file.xls]GA'!r1c2:r65536c9,7,FALSE)),(VLOOKUP(RC[-3],'file.xls]REBAR'!r1c2:r65536c10,9,FALSE)),(VLOOKUP(RC[-3],'file.xls]GA'!r1c2:r65536c9,7,FALSE)))))"</SPAN></SPAN>
 
Range("e6").Formula = "=Today()"</SPAN></SPAN>
Range("e:e").Select</SPAN></SPAN>
  Selection.Copy</SPAN></SPAN>
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _</SPAN></SPAN>
     :=False, Transpose:=False</SPAN></SPAN>
 
Range("e7", Range("e7").End(xlDown)).Name = "New"</SPAN></SPAN>
 
Range("e6").Select</SPAN></SPAN>
    With Selection</SPAN></SPAN>
        .HorizontalAlignment = xlCenter</SPAN></SPAN>
        .VerticalAlignment = xlBottom</SPAN></SPAN>
        .WrapText = False</SPAN></SPAN>
        .Orientation = -90</SPAN></SPAN>
        .AddIndent = False</SPAN></SPAN>
        .IndentLevel = 0</SPAN></SPAN>
        .ShrinkToFit = False</SPAN></SPAN>
        .ReadingOrder = xlContext</SPAN></SPAN>
        .MergeCells = False</SPAN></SPAN>
    End With</SPAN></SPAN>
    Rows("6:6").RowHeight = 59</SPAN></SPAN>
 
For Each Cell In Range("New")</SPAN></SPAN>
Cell.Select</SPAN></SPAN>
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="=RC[1]"</SPAN></SPAN>
     With Selection.Interior</SPAN></SPAN>
        .ColorIndex = 42</SPAN></SPAN>
        .Pattern = xlSolid</SPAN></SPAN>
    End With</SPAN></SPAN>
 
Next</SPAN></SPAN>
 
 
End Sub</SPAN></SPAN>

PS. I have also posted this on excelforum.com, but for some reason our servers are not letting us on that site this morning, just incase the poeple who've already seen this wonder why I'm asking again on another forum, I can't read the repsonses.