One way:

Public Sub HighlightExistingValues()
On Error Resume Next 'in case no values
Cells.SpecialCells(xlCellTypeConstants).Interior.C olorIndex = 3
On Error GoTo 0
End Sub




In article ,
EJS wrote:

Newbie here looking for what I hope is a simple solution to a problem.

What I am trying to do is create a macro that will closely resemble the
function of "conditional formatting".

I will have several ranges of cells that may or may not have values in
them at a given time.

What I would like it to be able to press a button to launch a macro
that will seek out any cell with a value in it, and highlight that
cell. It sounds like conditional formatting will solve my problem but
doing that will mean if a value is entered into a blank cell, it will
automatically format it because of the condition.

The point is, I only want the cells to be highlighted AFTER I run the
macro so any new values will not be highlighted right away.

Any ideas on how I can do this? Is it possible to make an IF THEN
statement with logic? IF cell has value, THEN highlight cell yellow?


I hope I am getting my idea across. Any help would be appreciated.

Thanks,

EJ