Macro to Highlight

[EJS]

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

 

[Chip Pearson]

I'm not entirely clear why you don't use Conditional Formatting,
but you might try some code like the following:

Dim Rng As Range
For Each Rng In Range("A1:A10")
If Rng.Value <> "" Then
Rng.Interior.ColorIndex = 3 'red
End If
Next Rng



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[JE McGimpsey]

One way:

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

 

[Corey]

Why don't you set the 1st Cond. Format to have the font/shade/border left as
the rest of your sheet is, and
use the 2nd condition, to shade or change the font colour to suit?

 

[Leith Ross]

Hello EJ,

Copy this code then paste it into a standard VBA Module.This macro will
examine all cells in the Range you specify and highlight non empty cells
in Yellow.

To Add A VBA Module:
1) Press Alt + F11 while in Excel to launch the VB Editor
2) Press Alt + I to activate the Insert Menu
3) Press M to add a new Module to your Project.


Code:
--------------------

Public Sub HighlightCells(Rng As Range)

Dim Cell As Range

For Each Cell In Rng
If Cell.Value <> "" Then
Cell.Interior.Color = RGB(255, 255, 0)
End If
Next Cell

End Sub

--------------------


Calling the Macro:
HighlightCells Range("A1100")


Sincerely,
Leith Ross

 

[EJS]

Thank you for the replies.

I will try the above and see how it works for me. I will get back to
you with results.

In reply to Chip Pearson, I cant use Conditional Formatting because
each time a blank cell in the range has a value input into it in the
range, they would be formatted automatically from the conditional
formatting. I only need them highlighted after the macro is run but
not every time a value is entered. I know it seems confusing when I
read it myself.