Pasting in Conditional Formatting

[Corky4661]

I am not sure if this is possible or not. I have a sheet that in column D, the names actually start in cell D9. They are random names and some are duplicate. The information is taken from another sheet and pasted here. I have conditional formatting to color code each name now. However I have to manually put in each name. So what I am attempting to do it make it automatic.

I highlight a name in the sheet1 and hit a button that includes the macro/formula to copy the name from the formula bar, and then open conditional formatting, type in =$D9="paste name here from clip board". Then color the name.

I think I have everything except how to paste into conditional formatting from the clipboard.

Is there anyway to do this? Any help would be so greatly appreciated!

Sub nightpath()
'
' nightpath Macro
'

'
Dim strFormula As String
Dim objDataObj As Object
'\Check that single cell is selected!
If Selection.Cells.Count > 1 Then
MsgBox "Select single cell only!", vbCritical
Exit Sub
End If
'Check if we are not on a blank cell!
If Len(ActiveCell.Formula) = 0 Then
MsgBox "No Formula To Copy!", vbCritical
Exit Sub
End If
'Add quotes as required in VBE
strFormula = Chr(34) & Replace(ActiveCell.Formula, Chr(34), Chr(34) & Chr(34)) & Chr(34)
'This is ClsID of MSFORMS Data Object
Set objDataObj = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
objDataObj.SetText strFormula, 1
objDataObj.PutInClipboard
MsgBox "Name copied to Clipboard!", vbInformation
Set objDataObj = Nothing
Range("A9:I500").Select
ActiveWindow.ScrollRow = 468
ActiveWindow.ScrollRow = 466
ActiveWindow.ScrollRow = 462
ActiveWindow.ScrollRow = 414
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 9
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$D9="Paste name from clipboard here"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub