Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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 "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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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 *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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 "Goverdhan Kudligi" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
compare cell valeus and highlight row
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 "Goverdhan Kudligi" wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare & highlight changes between two worksheets? | Excel Worksheet Functions | |||
I want to compare old and new datasheets and highlight changes | Excel Worksheet Functions | |||
Compare and Highlight Rows | New Users to Excel | |||
Compare two Cells and highlight third cell | Excel Worksheet Functions | |||
Compare Two Worksheets and Highlight Changes | Excel Programming |