The ExcelHelpDesk Support team received the following request for help
Problem – Highlight a Row based on Values in the Row
I want to hi-lite a row where the value in colum k is different than the value in colum m in the specified row the value may be the same in k2 and k3 therefore duplicates does not work only if the value in the specific row are not the same it must be hi-lited and if k=m it must be removed.
I need an automated way to hi-lite these rows as the check must be done across 20,000 rows in a worksheet. Thanks for any help you can provide.
For this problem we received a sample workbook that provided the starting point. You can see in the screen shot below that the data contained a number of rows and the key columns of K and M are to be compared to determine if a row should be removed. The formatting of some rows was set as an example.
To solve this request we decided to use a macro that would run across the worksheet. It would look at the values in each column and depending on the comparison would set a value to indicate a “match” or “different” it would also set the entire row fill color.
Step 1 – Create Macro to Compare and Set Format
The following VBA macro can be copied into any module within the workbook that needs perform the compare between the values. Note that the columns to be compared can be changed by modifying the constant at the the start of the routine. For this solution we have also added a value that results from the compare to be “matched” or “different”. That value once set can be used to filter the rows that need to be deleted from the worksheet.
Open the workbook with the sheets that are to be printed and start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert > Module. Copy the following code into the new Module
'The following routine will check values
Dim myRange As Range
Dim i As Long
Const strValueOne = "K1"
Const strValueTwo = "M1"
Set myRange = ThisWorkbook.Worksheets("Sheet1").Range(strValueOne)
i = 0
Do While myRange.Offset(i, 0).Value <> ""
If IsNumeric(myRange.Offset(i, 0).Value) And IsNumeric(myRange.Offset(i, 2).Value) Then
If myRange.Offset(i, 0).Value = myRange.Offset(i, 2).Value Then
myRange.Offset(i, 3).Value = "Matched"
myRange.Offset(i, 0).EntireRow.Interior.Color = vbRed
Else
myRange.Offset(i, 3).Value = "Different"
myRange.Offset(i, 0).EntireRow.Interior.Color = vbYellow
End If
End If
i = i + 1
Loop
End Sub
Step 2 – Running the Macro
Once the Macro is copied into your workbook you can run it from within the Code Module by selecting “Run”. You can also go back to the worskheet and select the Developer Tab > Macros > Run after selecting the macro “CheckAndHighlightRows”.
Once the Macro has run the worksheet should be updated with the relevant highlighting and the value of the comparison set to show if the values were “matched” or “different”.
Finished – Sample Workbook including Macro
Here is a link to a Sample Workbook that includes the Macro described above
| Download Sample Workbook |
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment
