compare cell valeus and highlight row

  • Thread starter Thread starter amanda
  • Start date Start date
A

amanda

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?
 
Click on the Row Header in Row 2 which will highlite the complete Row 2

Format, Conditional Formatting.. Select FormulaIs and enter
=AND($A2>=3,$F2=0) in the box - Select Color Pattern

And OK OUT...

Click again on the Row Header of Row 2 - Click the format-Painter Icon
and click on Row-Header 3 and drag down to Rowheader 100; that should
get you started.

HTH,

Jim May
 
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
 
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





- Show quoted text -
Click to expand...

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!!
 
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
 
Hi,

I would like know how to compare a cell value with range of cell values
and accordingly assign a value to another cell in the same row as the
first cell.

example,

compare cell a10 with cell range sheet2(c10:c30) and assign cell b10
with cell sheet2(b10)

regards
 
Hi Goverdhan,

I'm not sure that I fully understand your question.

Do you mean in your example that you want to find the value of
sheet1.range(A10) in sheet2.range(c10:c30)
and then copy the value from from column B and the row where the match is
found to sheet1.range(B10).

Regards,

OssieMac
 
Afterthought. Are you looking for a macro to do what you want and do you want
it to loop so that it looks up all the values in column A?

Regards,

OssieMac
 
Back
Top