problems comparing cells in seperate columns

jeasterly

New Member
Joined
Oct 24, 2006
Messages
28
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!
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Why can't you use conditional formatting?

=C2>B2*1.2
 

jeasterly

New Member
Joined
Oct 24, 2006
Messages
28
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-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Well just add code to do the conditional formatting to the macro.:)
 

jeasterly

New Member
Joined
Oct 24, 2006
Messages
28

ADVERTISEMENT

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

thx
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 24, 2006
Messages
28

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
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?
Code:
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

New Member
Joined
Oct 24, 2006
Messages
28
I get an error invalid outside procedure at the line
Set ws = Worksheets("CTO_Results")
and the SET is highlighted.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,918
Office Version
  1. 365
Platform
  1. Windows
Well that's probably because I ommitted the sub header.:oops:

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

Watch MrExcel Video

Forum statistics

Threads
1,114,040
Messages
5,545,676
Members
410,697
Latest member
srishtijain0708
Top