Hi again Amanda,

Try code below and it should now work for you.

A few notes to help you understand the code and avoid any confusion.
c1 is used as a variable and is nothing to do with cell C1. It can be any
name except some specific reserved words.

The column Id had to be changed in 2 places. "A1" to "C1" and the column
number in Cells(Rows.Count, 1) to Cells(Rows.Count, 3).

The Offset to column F in the If statement then had to be changed from
Offset(0, 5) to Offset(0, 3) because column F is now only 3 steps across from
column C. Previously it was 5 steps across from A. (Offset is counted as the
number of times you would have to press the right arrow to move to the
column.)

Percentages are actually decimal fractions. 3% is actually 0.03 and that is
how it is handled in the code. Alternatively you can use 3/100. Zero% of
course is zero and doesn't require any conversion.

I have changed the line of code to set the interior color so that if you
don't want yellow, then you can look up 'color index' in VBA help to find the
index number for an alternative color. (Note that it is the American spelling
of color not the British colour). Also you need to open Help while in the VBA
editor not from the spreadsheet because they are two different helps. I have
had people run into this problem before and could not find what they required.

A space followed by an underscore (' _') at the end of a line is simply a
break in the line of code that would otherwise be one line.

Sub Compare_Cells()

Dim rnge1 As Range
Dim c1 As Range

'Find the last cell with data in column C.
'and name range from cell C1 to last cell with data.
Set rnge1 = Sheets("Sheet1"). _
Range("C1", Cells(Rows.Count, 3) _
.End(xlUp))

'Loop through each cell in range
For Each c1 In rnge1

'Test value of c1 and value of cell in column F
If c1.Value = 0.03 And c1.Offset(0, 3) = 0 Then

Rows(c1.Row).Interior.ColorIndex = 6 'Yellow

End If

Next c1

End Sub

Feel free to get back to me if you still have problems or want some more
answers.

Regards,

OssieMac


"amanda" wrote:

On Jul 26, 10:10 pm, OssieMac
wrote:
Hi Amanda,

The following macro should put a fill color of yellow on all the rows where
value in column A is = 3 and value in corresponding row in column F is zero.

Sub Compare_Cells()

Dim rnge1 As Range
Dim c1 As Range

'Find the last cell with data in column A.
'and name range from cell A1 to last cell with data.
Set rnge1 = Sheets("Sheet1").Range("A1", Cells(Rows.Count, 1) _
.End(xlUp))

'Loop through each cell in range
For Each c1 In rnge1

'Test value of c1 and value of cell in column F
If c1.Value = 3 And c1.Offset(0, 5) = 0 Then

Rows(c1.Row).Interior.Color = 65535

End If

Next c1

End Sub

I will appreciate it if you let me know if it works.

Regards,

OssieMac



"amanda" wrote:
I would like to highlight a row in excel using a macro that would
compare the cell value in one colum to the cell value in another
column in the same row. Specifically, if the cell value in column A
is 3 or greater and the cell value in column F is 0 I want this row to
stand out. Any ideas?- Hide quoted text -

- Show quoted text -

Hi there, thanks for the response!

A couple of qualifiers. The colums I'm trying to compare are colums C
and F both of which contain perctentages. So, if the cell value of C
= 3% or greater AND the cell value of data in column F = 0% then
highlight. I tried changing the column to C but I think I'm not
getting something as I am getting a syntax error in the 6th line
starting with "Set rnge1 =....".

Thanks!!