Excel Macro using conditional formatting question 1

[ITALIAORIANA]

Hi,

I have a spreadsheet in excel 2003 that I have conditional formatting =NOT(B2=C2) to highlight the differences. I recorded a macro to format the rest of the columns like this.

I did each column individually because I don't know any other way to do it. I only got to column BI but doing it like that is a long tedious process. Is there an easier way to use the conditional formatting for all columns but keep the original format of the cell? Some of the fields are alpha numeric, some are dates, some are numbers, etc so for example I would still need the dates to be formatted as dates. I tried to just copy and paste the conditional formatting for the entire spreadsheet but the dates were no longer formatted as dates. I have posted the macro I currently have.

Any help would be greatly appreciated.

Thanks
Deana

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7 _
, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array _
(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), _
Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), Array( _
27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array(33, 1), _
Array(34, 1), Array(35, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Range("B2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(B2=C2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("C2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(C2=B2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("C:C").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("D2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(D2=E2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Range("D2").Select
Selection.Copy
Columns("D").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("E2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(E2=D2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("D2").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("F2").Select
Selection.Copy
Columns("G:G").Select
Range("F2").Select
Application.CutCopyMode = False
Selection.Copy
Range("G2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(G2=F2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("G:G").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=7
Range("H2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(H2=I2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Range("H2").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("I2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(I2=H2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("I:I").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("H2").Select
Selection.Copy
Columns("J:J").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("J2").Select
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(K2=J2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Range("K2").Select
Selection.Copy
Columns("K:K").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("L2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(L2=M2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Range("L2").Select
Selection.Copy
Columns("L:L").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("L2").Select
Selection.Copy
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(M2=L2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Range("M2").Select
Selection.Copy
Columns("M:M").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("N2").Select
ActiveWindow.SmallScroll ToRight:=3
Range("L2").Select
Selection.Copy
Columns("N:N").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("N2").Select
Selection.Copy
Range("O2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(O2=N2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Range("O2").Select
Selection.Copy
Columns("O:O").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("P2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(P2=Q2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("P").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=5
Range("P2").Select
Selection.Copy
Range("Q2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(Q2=P2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("Q:Q").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("R2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(R2=S2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("R:R").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("R2").Select
Selection.Copy
Range("S2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(S2=R2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("S:S").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("R2").Select
Selection.Copy
Columns("T:T").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("T2").Select
ActiveWindow.SmallScroll ToRight:=3
Selection.Copy
Range("U2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(U2=T2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("U:U").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("V2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(V2=W2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("V:V").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("V2").Select
Selection.Copy
Range("W2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(W2=V2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("W:W").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=4
Range("X2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(X2=Y2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("X:X").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("X2").Select
Selection.Copy
Range("Y2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(Y2=X2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("Y:Y").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("X2").Select
Selection.Copy
Columns("Z:Z").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("Z2").Select
Selection.Copy
Range("AA2").Select
Application.CutCopyMode = False
Range("Z2").Select
Selection.Copy
Range("AA2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AA2=Z2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AA:AA").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("Z2").Select
Selection.Copy
Columns("AB:AB").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AB2").Select
Selection.Copy
Range("AC2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(AC2=AB2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AC:AC").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=4
Range("AD2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(AD2=AE2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AD:AD").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AD2").Select
Selection.Copy
Range("AE2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(AE2=AD2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AE:AE").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AD2").Select
Selection.Copy
Columns("AF:AF").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AF2").Select
Selection.Copy
Columns("AG:AG").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AF2").Select
Application.CutCopyMode = False
Selection.Copy
Range("AG2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NOT(AG2=AF2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AG:AG").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SmallScroll ToRight:=3
Range("AH2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AH2=AI2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AH:AH").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AH2").Select
Selection.Copy
Range("AI2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AI2=AH2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AI:AI").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AJ2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AJ2=AK2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AJ:AJ").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AK2").Select
Selection.Copy
Range("AK2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AK2=AJ2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AK:AK").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False





Range("AL2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AL2=AM2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AL:AL").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AM2").Select
Selection.Copy
Range("AM2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AM2=AL2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AM:AM").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("AN2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AN2=AO2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AN:AN").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AO2").Select
Selection.Copy
Range("AO2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AO2=AN2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AO:AO").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Range("AP2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AP2=AQ2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AP:AP").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AQ2").Select
Selection.Copy
Range("AQ2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AQ2=AP2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AQ:AQ").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Range("AR2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AR2=AS2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AR:AR").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AS2").Select
Selection.Copy
Range("AS2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AS2=AR2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AS:AS").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Range("AT2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AT2=AU2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AT:AT").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AU2").Select
Selection.Copy
Range("AU2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AU2=AT2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AU:AU").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Range("AV2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AV2=AW2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AV:AV").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AW2").Select
Selection.Copy
Range("AW2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AW2=AV2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AW:AW").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AX2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AX2=AY2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AX:AX").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("AY2").Select
Selection.Copy
Range("AY2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AY2=AX2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AY:AY").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AZ2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(AZ2=BA2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("AZ:AZ").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("BA2").Select
Selection.Copy
Range("BA2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BA2=AZ2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BA:BA").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BB2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BB2=BC2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BB:BB").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("BC2").Select
Selection.Copy
Range("BC2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BC2=BB2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BC:BC").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BD2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BD2=BE2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BD:BD").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("BE2").Select
Selection.Copy
Range("BE2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BE2=BD2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BE:BE").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BF2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BF2=BG2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BF:BF").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("BG2").Select
Selection.Copy
Range("BG2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BG2=BF2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BG:BG").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("BH2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BH2=BI2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BH:BH").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("BI2").Select
Selection.Copy
Range("BI2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(BI2=BH2)"
Selection.FormatConditions(1).Interior.ColorIndex = 24
Selection.Copy
Columns("BI:BI").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
End Sub

 

[AnotherHiggins]

Wow. That's a lot of code.

Please post VBA-specific questions in forum707. And when you post there, you might want to start off by just posting the part that's giving you trouble. Also, wrap your code in [ignore]

[/ignore] tags (click on the "Process TGML" link below the posting window for more formatting options).

But before we get into all that, back up and describe what you're trying to do with Conditional Formatting, because you should be able to do the entire table in one shot.

If I understand what you're after, the CF formula will either be:
[tab]=NOT($B2=C2)
or
[tab]=NOT($B$2=C$2)

NOTE: When using Absolute References like this, it is important which cell you have activated. If you're applying this CF to columns B:BI, then you'd want B2 active.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ITALIAORIANA]

Thanks, I posted this in the correct forum.

 

[AnotherHiggins]

Any luck just using straight Conditional Formatting?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ITALIAORIANA]

Hi John,

In this spreadsheet I have to compare certain columns to each other. Like B to C which the basic conditional formatting works great. But it doesn't highlight the cells in both B and C, so in order to get C cells highlighted as well, I used basic conditional formatting in C too.

This is great and works perfectly but now I have to do the entire spreadsheet (All columns D to E, F to G, and so on). I was trying to find a quicker easier way to format B to C then C to B. I have been doing each column individually and am up to column BI but it is taking forever.

Thanks
Deana

 

[AnotherHiggins]

I just replied in the other thread, too.

What I'm tryinig to tell you is that you can apply the CF for all of your columns at the same time. You don't need to do one column at a time.

If you want to highlight both of the cells that don't equal each other, select columns B:BI, ensuring that B1 is the active cell (the one cell that isn't shaded), then go to Format > Conditional Formatting and for the formula, use [COLOR=blue white]=or(not(B1=A1), not(B1=C1))[/color].

Exactly what the formula will be depends on exactly what you want to do.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ITALIAORIANA]

Hi John,

I know what you are saying and I am really sorry if I am frustrating you, but I thought I understood conditional formatting - until today.

I will try to explain better (so far I am not doing such a good job)

I formatted column B using =not(B1=C1)to highlight blue. I then copied this format across the spreadsheet. I needed to see the following:

B1 to C1
D1 to E1
F1 to G1

Doing the copy and paste I got:

B1 to C1
C1 to D1
D1 to E1

I also got the same results if I selected the entire range of cells. It kept going in succession comparing the columns. So that's why I did it manually. I didn't know if there was a formula to use to get it to do what I wanted.

Then I noticed that the differences were only highlighted in one column. Example: B and C were compared and differences were only highlighted in B. They want both B and C highlighted. So since I obviously need a class on this which is extremely apparent today. I went back and did the conditional formatting for the rest of the columns so they would be highlighted too.

It was taking forever so I thought I'd post here and ask for an easier way.

LOL, I had no idea I was going to have to enroll in night school for a course on excel.

Anyway, thanks for your help, I really do appreciate it.

 

[AnotherHiggins]

No problem, I just hate to see you spin your wheels with a hundred lines of code if there's an easy, built-in way to do what you want.

I think I understand now.... But do you want to continue the pattern for which cells get highlighted? That is do you want to check B, D, F, H, J, K.... - basically all the even columns? If so, then the 'even column' bit can work to our advantage here.

Try this formula:
[tab][COLOR=blue white]=And(Mod(Column(B1), 2) = 0, Not(B1 = C1))[/color]

The 'Mod(Column(B1), 2) = 0' bit only returns TRUE for even columns.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ITALIAORIANA]

Hi John,

This is FABULOUS!!! This would have saved me so much work. Yes, I was checking all the even columns and comparing them to the odd ones to the right.

Now all the even column differences are highlighted. Is there a way to modify the formula to get the odd column counterparts highlighted as well? Example: If B1 and C1 are different, they want both B1 and C1 highlighted.


Thanks
Deana

 

[AnotherHiggins]

There sure is!

Two ways:

1) Add a second Condition

Go back into Format > Conditional Format, then click on Add. The second formula will be the second half of the OR statement above:
[tab][COLOR=blue white]And(Mod(Column(B1), 2) = 1, Not(B1 = A1)))[/color]

2) Just make a big ol' OR statement.

Replace the formula you're using now with:
[tab][COLOR=blue white]=Or(And(Mod(Column(B1), 2) = 0, Not(B1 = C1)), And(Mod(Column(B1), 2) = 1, Not(B1 = A1)))[/color]

Oh, and personally I'd use A1 <> B1 instead of the Not statements, but the above formulas should work for you.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ITALIAORIANA]

Hi John,

U ROCK!!! Thank you so much for being patient and helping me. You have just saved me HOURS of work.
I wish I could give you a million stars for this. It works EXACTLY the way I need it to.

Thanks
Deana

 

[AnotherHiggins]

Glad I could help


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.