Excel macro using conditional formatting question 1

[ITALIAORIANA]

Hi,

I posted this in the wrong forum originally. I re-posted here. I am having computer issues so please forgive me if it shows up twice.

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. In the example you will see that BI is compared to BH, but then I have to compare BH to BI so that both columns will be highlighted if there is a difference. I have posted a portion of the macro I currently have.

Any help would be greatly appreciated.

Thanks
Deana

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

 

[SkipVought]

"I did each column individually because I don't know any other way to do it."

You select the entire range you want to format, starting in the UPPER L-H corner, which cell is then the ActiveCell.

[v]You code your CF as if only that cell were selected[/b], with the understanding that the entire range is being included. So any reference to a cell in the sheet may need an ABSOLUTE row or column reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ITALIAORIANA]

Hi Skip,

I tried this but instead of BI comparing to BH and then BH comparing to BI to get both highlighted. BH will compare to BI and then BI will compare to BJ and so on. In order for me to get it to work the way I need to, I have to manually change the conditional formatting and then re-format the columns again. I was looking for a way to avoid doing all the manual stuff.

Thanks
Deana

 

[AnotherHiggins]

-> In order for me to get it to work the way I need to, I have to manually change the conditional formatting and then re-format the columns again.

No, you don't. As I asked back in thread68-1484508, why not back up and tell us exactly what you're after. You can CF the entire table in one go. It's a matter of which cell is selected when you apply your CF, and whether you use ABSOLUTE or RELATIVE referencing.

If you always want to compare to the column to the left, then you would select columns B:BI, ensureing that B1 is the active cell (the one cell that isn't shaded), then go to Format > Conditional Formatting and for the formula, use =not(B1=A1).

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.