Macro needed to compare multiple cells

I am new to Excel macros and am currently right now trying to write a macro that would highlight a row if a condition existed. The best way that I can I explain is it as follows:

If
cell M >= (90% of Cell N) and cell O >= (90% of Cell N)
then
Highlight the row

I have over 3400 rows and I have concluded that writing a macro would be the most efficient way. I am open to any suggestions. Thanks in advance

I think this will do, let me know:

Sub spwolfe()
    Dim ws As Worksheet, Prcnt As Double, Ttl As Double
    Dim LRng As Variant, HRng As Variant, TtlRng As Variant, cl As Variant
    Set ws = Sheets("YourSheetNameHere")
    If Range("M1").Value = "" Then Set LRng = Range("M1").End(xlDown)
    Set HRng = Range("M10000").End(xlUp)
    Set TtlRng = Range(LRng, HRng)
    For Each cl In TtlRng
        If cl.Value >= (cl.Offset(0, 1).Value - (cl.Offset(0, 1).Value * 0.1)) And _
            cl.Offset(0, 2).Value >= (cl.Offset(0, 1).Value = (cl.Offset(0, 1).Value * 0.1)) Then cl.Interior.ColorIndex = 8
    Next cl
End Sub

Hi spwolfe, one thing you need to change and I'm sorry I didn't state it, is the sheets reference here:

Set ws = Sheets(6)

You need to change that to whatever sheet it is that you want this code to work on. I had it as 6 because that is where I created the mock data in my own workbook.

In fact, you should probably declare the worksheet, which I forgot to do also.

Code Change:

Sub spwolfe()
    Dim ws As Worksheet, Prcnt As Double, Ttl As Double
    Dim LRng As Variant, HRng As Variant, TtlRng As Variant, cl As Variant
    Set ws = Sheets(6)   ''Change this to your sheet.  If it has a name,  Set ws = Worksheets("WorksheetName")
    With ws
    If .Range("M1").Value = "" Then Set LRng = .Range("M1").End(xlDown)
    Set HRng = .Range("M10000").End(xlUp)
    Set TtlRng = Range(LRng, HRng)
    For Each cl In TtlRng
        If cl.Value >= (cl.Offset(0, 1).Value - (cl.Offset(0, 1).Value * 0.1)) And _
            cl.Offset(0, 2).Value >= (cl.Offset(0, 1).Value = (cl.Offset(0, 1).Value * 0.1)) Then
                cl.EntireRow.Interior.ColorIndex = 8
        Else: cl.EntireRow.Interior.ColorIndex = xlNone
        End If
    Next cl
    End With
End Sub

Perhaps if you can upload a mock workbook with non-personal data, this problem may be rectified.

Can you upload in advanced settings? I cannot access that page because it is blocked by my employer. Or perhaps someone else can get that file and try to fix it!

Hi spwolfe, that was much better for me. I made a slight ammendment to the code.

Sub spwolfe()
    Dim ws As Worksheet, Prcnt As Double, Ttl As Double
    Dim LRng As Variant, HRng As Variant, TtlRng As Variant, cl As Variant
    Set ws = Worksheets("Sheet1")   ''Change this to your sheet.  If it has a name,  Set ws = Worksheets("WorksheetName")
    With ws
        If .Range("M1").Value = "" Then
            Set LRng = .Range("M1").End(xlDown)
        Else: Set LRng = .Range("M1")
        End If
        Set HRng = .Range("M10000").End(xlUp)
        Set TtlRng = Range(LRng, HRng)
        For Each cl In TtlRng
            If cl.Value <> "" Then
                If cl.Value >= (cl.Offset(0, 1).Value - (cl.Offset(0, 1).Value * 0.1)) And _
                   cl.Offset(0, 2).Value >= (cl.Offset(0, 1).Value = (cl.Offset(0, 1).Value * 0.1)) Then
                    cl.EntireRow.Interior.ColorIndex = 8
                Else: cl.EntireRow.Interior.ColorIndex = xlNone
                End If
            End If
        Next cl
    End With
End Sub

I think the calculation is right but you may want to double or even triple check that the the proper rows are being highlighted. Good luck

As you approach the bottom, are there values in Columns M, N, & O that are not numerical?

Sorry spwolfe but I have to clarify, in your new upload you have data from Range("M1:037") and then data from range ("A40:C3409"). Should it all be in columns M, N, & O?

This works for me without error (and there are no spelling mistakes this time-sorry)

Sub spwolfe()
    Dim ws As Worksheet, Prcnt As Double, Ttl As Double
    Dim LRng As Variant, HRng As Variant, TtlRng As Variant, cl As Variant
    Set ws = Worksheets("Sorted")   ''Change this to your sheet.  If it has a name,  Set ws = Worksheets("WorksheetName")
    With ws
        If .Range("M1").Value = "" Then
            Set LRng = .Range("M1").End(xlDown)
        Else: Set LRng = .Range("M1")
        End If
        Set HRng = .Range("M10000").End(xlUp)
        Set TtlRng = Range(LRng, HRng)
        For Each cl In TtlRng
            If cl.Value <> "" Then
                If cl.Value >= (cl.Offset(0, 1).Value - (cl.Offset(0, 1).Value * 0.1)) And _
                   cl.Offset(0, 2).Value >= (cl.Offset(0, 1).Value = (cl.Offset(0, 1).Value * 0.1)) Then
                    cl.EntireRow.Interior.ColorIndex = 8
                Else: cl.EntireRow.Interior.ColorIndex = xlNone
                End If
            End If
        Next cl
    End With
End Sub

Hi spwolf, I wonder if it is a compatability issue. I am at work and I am using Office 2003 while you are using 2010 according to your info. In my version, there are no errors and the code moves fast through the workbook you provided in post #16. I'll ask for more assistance and hopefully there is something minor that can be worked out to get you up and running.

I just ran the same code from post #18 in the new upload from you and received 0 errors so I am at a loss for words right now. Sorry and thanks for YOUR patience on this matter.

No worries spwolfe, you have been very clear, perhaps my mind is not firing properly today.

You could test which rows should be highlighted with the following formula:

=IF(AND(M1="",N1="",O1=""),"",IF(AND( M1 >=N1*(90%), O1 >=N1*(90%)),"highlight","")) in row 1 and copy down.

The sample workbook and Mordred's code worked for me in Excel 2007 on Windows 7.

Regards

I just compared highlighted cells from my code to tiger's and they highlight the same so if the results aren't as you want them then you have given us the wrong criteria for what your output should be.

Regards:

Slightly modified version:

=AND($M1<>"",$N1<>"",$O1<>"",$M1>=$N1*0.9,$O1>=$N1*0.9)

select the entire columns A - O and apply at one time