Highlight Cells on a different worksheet if already highlighted (using conditional formatting) on another sheet

[sedmonson]

I am attempting to highlight cells where the data matches a different worksheet and these cells are already highlighted. Example; I have Ws1 with several columns of data. Ws1 highlights rows (based on conditional formatting) if a column matches data on Ws2. I now want to highlight Ws3 column E if the data in it matches Ws1 Column E and is highlighted. Since Ws1 highlight is based on conditional formatting, I attempted using various forms of a VBA for "Interior.Color" without any luck.

 

[rollis13]

If highlighting is done by CF you need to use .DisplayFormat.Interior.Color or .DisplayFormat.Interior.ColorIndex.

 

[sedmonson]

Thanks. That got me closer but for some reason it is highlighting the cells on Ws3 that are not highlighted with conditional formatting rather than the ones that are. Below is the current vba. Any ideas what I'm doing wrong?

Sub HighlightMatchedCells()

Dim sourceSheet As Worksheet
Dim targetSheet As Worksheet
Dim sourceRange As Range
Dim targetRange As Range
Dim cell As Range

Set sourceSheet = Worksheets("ECCN BLANK")
Set sourceRange = sourceSheet.Range("E2:E200")

Set targetSheet = Worksheets("EMAILS")
Set targetRange = targetSheet.Range("E2:E100")

For Each cell In targetRange.Cells

If cell.Interior.ColorIndex = sourceRange.Find( _
What:=cell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole).DisplayFormat.Interior.ColorIndex Then

' Highlight the matched cell
cell.Interior.Color = vbYellow

End If

Next cell

End Sub

 

[rollis13]

Sorry, without a dummy file I can't be of any help. I'm unable to match what you said in post #1 with the range references in your macro.