problems comparing cells in seperate columns

[jeasterly]

Hi all, I'm new to this but I can't find this specific question answered.

I have a spreadsheet that I am pulling into a workbook via macro that the data in Column B is the mfg cost : The data in Column C is the sales price.
I need the code to check if the value in C is 20% greater than the value in the corresponding B column. If it is NOT then shade the value in C yellow.

I can't use the conditional formatting since I am pulling the entire sheet in from an access query. Can anyone help. I am using Excel 2002.

Thanks so much!

 

[Norie]

Why can't you use conditional formatting?

=C2>B2*1.2

 

[jeasterly]

Each time the macro is ran the sheet is pulled in by running a macro that runs another macro in access which runs the query that creates the sheet. I tried doing the conditional formatting but each time the macro runs the formatting is removed.

 

[Norie]

Well just add code to do the conditional formatting to the macro.

 

[jeasterly]

Sounds like a plan... Only I dont know how to write the code for the conditional formatting that I need.

thx

 

[Norie]

Well it's probably worth recording a macro (Tools>Macro...Record New Macro...) when you do it manually.

That should generate code that you can integrate into the existing code.

Note it'll probably be altered, for example to take account of varying rows.

I'm just off now but give it a try and I'll check back later with some code.

By the way did you try the formula I posted?

If so did it work?

 

[jeasterly]

Thanks Norie -
by doing the macro I was able to scarf up the code. I know there has to be a way doing this for each cell that is shorter than what I have here. Any tips?
Worksheets("CTO_Results").Range("N2").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=M2*1.2"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(1).Interior.ColorIndex = 19
Worksheets("CTO_Results").Range("N3").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=M3*1.2"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(1).Interior.ColorIndex = 19
Worksheets("CTO_Results").Range("N4").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=M4*1.2"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
Selection.FormatConditions(1).Interior.ColorIndex = 19
Worksheets("CTO_Results").Range("N5").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=M5*1.2"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
End With
**************************etc....
Thanks for all your help.. I am new to this and just trying to fumble my way thru.

 

[Norie]

jeasterly

Here's what I came up with based on your code.

I'm not 100% sure about the formula though.

Does it work for your data?

Dim ws As Worksheet
Dim LastRow As Long

Set ws = Worksheets("CTO_Results")

With ws
    LastRow = .Range("N" & Rows.Count).End(xlUp).Row
    With .Range("N2").Resize(LastRow - 1)
        With .FormatConditions
            .Delete
            .Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=RC[-1]*1.2"
            With .Item(1).Font
                .Bold = True
                .Italic = False
            End With
        End With
    End With
End With

 

[jeasterly]

I get an error invalid outside procedure at the line
Set ws = Worksheets("CTO_Results")
and the SET is highlighted.

 

[Norie]

Well that's probably because I ommitted the sub header.

I did that because I assumed you would be placing this in an existing sub.