Advanced conditional formatting Excel 2007

[soswinglifeaway]

So I realize this may not be possible but it seems I am always more and more surprised at how advanced Excel is so I figured I'd give it shot. I have a list of about 2,500 names. About 300 of those names are bolded. I want Excel to look at those bolded items and apply conditional formatting if those bolded items have any duplicate values in the worksheet. In other words, even if "Smith" shows up 5 times in the workbook, Excel would not highlight it unless one of the bolded items were also equal to "Smith".


Here is the goal: comparing two lists and removing duplicate names (separated by first name/last name). I bolded the smaller list so I could scroll through and compare list 1 with names on list 2 and remove duplicates. I applied conditional formatting to highlight duplicate values in the last name field so that I could quickly see which names on list 1 were a possible match to names on list 2. The problem is there are so many people with the same last names (from the first, much bigger list) that lots of names are highlighted that don't need to be. So I want Excel to read the bolded cells and only highlight cells with duplicate values of those specific cells.


I hope that makes sense. It seems complicated and I'm not positive it's possible, but any help would be appreciated!

 

[Hui]

Soswinglifeaway


Firstly, Welcome to the Chandoo.org forums


What you want can't be done natively by Excel


You can use a UDF to read if a cell is Bold and then use that in a CF

The UDF is below


Say your CF range is A5:D10


Select A5:D10

Goto CF

Add a new CF using a Formula

use =isBold(D5)

Apply whatever format you want

[pre]

Function isBold(myCell As Range)as Boolean
isBold = False
If myCell.Font.Bold = True Then isBold = True
End Function

[/pre]

You can also use the UDF in cells as part of a formula if you want

 

[vijay.vizzu]

Dear soswinglifeaway


please try below code. it will highlight the bolded font in the range in red color. please change as per your requirement.

[pre]

Sub HighlightBold()
Dim cell As Range
Dim boldedRange As Range

Set boldedRange = Range("b1:b10")

For Each cell In boldedRange
If cell.Font.Bold = True Then cell.Font.Color = vbRed
Next
End Sub

[/pre]