View Full Version : Macro: Highlight Cell in Col B that DOES NOT exist in Col A

07-08-2011, 09:30 AM

I am trying to write a macro and I think I am going about it the wrong way. Here is an example of what I am trying to do:

Column A contains 500 cells of unique data
Column B contains 100 cells

I want to write a macro so that it takes the first cell of Column B and looks to see if it exisit in Column A. If it does not, then it highlights it, say Bold.

I then moves on to the second cell of Column B and goes down the list until the end.

Col B will remain static, but I will be pasting new data into Column A and re-running this process.

I was thinking that I will need to do a loop within a loop, but I can't quite picture how it should work.

Can anyone help me please?



07-08-2011, 12:37 PM
Sub Highlight_Differences()

dim cell as Range, cell2 as Range

for each cell in Range("B1:B100")
for each cell2 in Range("A:A")
if cell.value <> cell2.value
cell.Font.Bold = True
end if
next cell2
next cell

End Sub

Kenneth Hobs
07-08-2011, 01:29 PM
In this example, I skipped row 1.
Sub BoldEm()
Dim cell As Range, look As Range, f As Range
Set look = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
Set f = look.Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
cell.Font.Bold = False
If Not f Is Nothing Then cell.Font.Bold = True
Next cell
End Sub

Zack Barresse
07-09-2011, 10:16 AM
Why aren't you just using conditional formatting for this? Why are you wanting to use VBA code? Seems like a bazooka for a knife fight....