Compare COL D to COL F and if NOT a match, highlight cell in Col F

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
Need to compare Col D's % value to Col F's % value, if they do not match, highlight the cell in Col F yellow using vba.

Ideas?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,343
Does it have to be a VBA solution? You can do this with conditional formatting
 

hippiehacker

Well-known Member
Joined
Aug 2, 2011
Messages
1,911
this should do just modify the RGBs for the color I'm to lazy now to look them up

Code:
Sub compare_cols()
Application.ScreenUpdating = False
Set Report = Excel.Worksheets("Sheet1") 'You could also use Excel.ActiveSheet _
                                            if you always want this to run on the current sheet.

    lastRow = Report.UsedRange.Rows.Count

    

    For i = 2 To lastRow
            If Report.Cells(i, 4).Value <> "" Then 'This will omit blank cells at the end (in the event that the column lengths are not equal.
                If Report.Cells(i, 4).Value <> Report.Cells(i, 6).Value Then
                    Report.Cells(i, 6).Interior.Color = RGB(156, 0, 6) 'Dark red background
                    Report.Cells(i, 6).Font.Color = RGB(255, 199, 206) 'Light red font color
                End If
            End If
    Next i
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Here is another macro which you can also consider...

Code:
Sub HighlightNonMatchingDFcells()
  Dim UnusedColumn As Long
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Intersect(Columns(UnusedColumn), ActiveSheet.UsedRange.EntireRow)
    .FormulaR1C1 = "=IF(RC4<>RC6,""X"","""")"
    .Value = .Value
    With Intersect(.SpecialCells(xlConstants).EntireRow, Columns("F"))
      .Interior.Color = RGB(156, 0, 6) 'Dark red background
      .Font.Color = RGB(255, 199, 206) 'Light red font color
    End With
    .Clear
  End With
End Sub
 

hippiehacker

Well-known Member
Joined
Aug 2, 2011
Messages
1,911
Here is another macro which you can also consider...

Code:
Sub HighlightNonMatchingDFcells()
  Dim UnusedColumn As Long
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Intersect(Columns(UnusedColumn), ActiveSheet.UsedRange.EntireRow)
    .FormulaR1C1 = "=IF(RC4<>RC6,""X"","""")"
    .Value = .Value
    With Intersect(.SpecialCells(xlConstants).EntireRow, Columns("F"))
      .Interior.Color = RGB(156, 0, 6) 'Dark red background
      .Font.Color = RGB(255, 199, 206) 'Light red font color
    End With
    .Clear
  End With
End Sub

i knew there was some faster code :)
 

ChrisOK

Well-known Member
Joined
Mar 26, 2003
Messages
599
THANKS GREATLY to both/all 4 of you --- HOWEVER! 2 things:
The highlighting was supposed to be "Yellow" (and)

I found that HippieHacker's worked as long there were values -- but did not catch the variances where Col D was blank and Col E had a value.
Rick's caught the blanks and colorized them. (this was definitely important in this circumstance)

*The thing I forgot to mention was that I need it to by-pass header info and start at Row 4... Can this be easily incorporated?
 
Last edited:

Forum statistics

Threads
1,136,433
Messages
5,675,829
Members
419,586
Latest member
RoteichA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top