Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare values in a column
I need help with a macro that checks that cells values in column A are
increasing by one increment. In Column A i put receipts numbers. I want to be able to check if all receipts have been recorded. for example, if column A looks like this: A 1 100 2 101 3 103 4 104 5 105 All values are increasing by 1, except the value in cell A3 increased by 2. Which means that Receipt Number 102 hasn't been recorded. I want the macro to go through cells in column A, and once it detects a gap, the cell fill colour (cell A3 in the above example) turns red. I'm using Excel 2003. Many thanks Tendresse |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare values in a column
Perhaps consider conditional formatting. Assuming your data is in A1:A100,
select cells A2:A100, then Format/Conditional Formatting Formula Is: =a2-a1<1 then select Format/Pattern-Red. OK out. "Tendresse" wrote: I need help with a macro that checks that cells values in column A are increasing by one increment. In Column A i put receipts numbers. I want to be able to check if all receipts have been recorded. for example, if column A looks like this: A 1 100 2 101 3 103 4 104 5 105 All values are increasing by 1, except the value in cell A3 increased by 2. Which means that Receipt Number 102 hasn't been recorded. I want the macro to go through cells in column A, and once it detects a gap, the cell fill colour (cell A3 in the above example) turns red. I'm using Excel 2003. Many thanks Tendresse |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare values in a column
But if you really need a macro - perhaps this will point you in the right
direction Sub Test() Dim rngData As Range Dim i As Long Set rngData = Worksheets("Sheet2").Range("A1:A5") For i = 2 To rngData.Rows.Count If rngData(i).Value - rngData(i - 1).Value < 1 Then rngData.Cells(i).Interior.ColorIndex = 53 Else: rngData.Cells(i).Interior.ColorIndex = xlNone End If Next i End Sub "Tendresse" wrote: I need help with a macro that checks that cells values in column A are increasing by one increment. In Column A i put receipts numbers. I want to be able to check if all receipts have been recorded. for example, if column A looks like this: A 1 100 2 101 3 103 4 104 5 105 All values are increasing by 1, except the value in cell A3 increased by 2. Which means that Receipt Number 102 hasn't been recorded. I want the macro to go through cells in column A, and once it detects a gap, the cell fill colour (cell A3 in the above example) turns red. I'm using Excel 2003. Many thanks Tendresse |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare values in a column
Test on Sheet1
Sub testing() Const sh1Name = "Sheet1" Const colA = "A" Dim sh1 As Worksheet Dim i As Long Dim lastRowInColA As Long Set sh1 = Worksheets(sh1Name) lastRowInColA = Cells(Rows.Count, colA).End(xlUp).Row For i = 2 To lastRowInColA If sh1.Range(colA & i).Offset(1, 0).Value - sh1.Range(colA & i).Value 1 Then sh1.Range(colA & i).Offset(1, 0).Interior.ColorIndex = 6 End If Next i End Sub "Tendresse" wrote: I need help with a macro that checks that cells values in column A are increasing by one increment. In Column A i put receipts numbers. I want to be able to check if all receipts have been recorded. for example, if column A looks like this: A 1 100 2 101 3 103 4 104 5 105 All values are increasing by 1, except the value in cell A3 increased by 2. Which means that Receipt Number 102 hasn't been recorded. I want the macro to go through cells in column A, and once it detects a gap, the cell fill colour (cell A3 in the above example) turns red. I'm using Excel 2003. Many thanks Tendresse |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare values in a column
Hi JMB,
Thanks a lot for your reply. The macro worked perfectly. That's exactly what i wanted. You are a champion. "JMB" wrote: But if you really need a macro - perhaps this will point you in the right direction Sub Test() Dim rngData As Range Dim i As Long Set rngData = Worksheets("Sheet2").Range("A1:A5") For i = 2 To rngData.Rows.Count If rngData(i).Value - rngData(i - 1).Value < 1 Then rngData.Cells(i).Interior.ColorIndex = 53 Else: rngData.Cells(i).Interior.ColorIndex = xlNone End If Next i End Sub "Tendresse" wrote: I need help with a macro that checks that cells values in column A are increasing by one increment. In Column A i put receipts numbers. I want to be able to check if all receipts have been recorded. for example, if column A looks like this: A 1 100 2 101 3 103 4 104 5 105 All values are increasing by 1, except the value in cell A3 increased by 2. Which means that Receipt Number 102 hasn't been recorded. I want the macro to go through cells in column A, and once it detects a gap, the cell fill colour (cell A3 in the above example) turns red. I'm using Excel 2003. Many thanks Tendresse |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up values in one column and compare with another | New Users to Excel | |||
HOW TO COMPARE COLUMN VALUES IN EXCEL? | Excel Worksheet Functions | |||
How do I compare string values in one column to another column? | Excel Worksheet Functions | |||
Need code to compare cell values in a column | Excel Programming | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions |