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


--
EJS
------------------------------------------------------------------------
EJS's Profile: http://www.excelforum.com/member.php...o&userid=34918
View this thread: http://www.excelforum.com/showthread...hreadid=546565

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


"EJS" wrote in
message ...

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


--
EJS
------------------------------------------------------------------------
EJS's Profile:
http://www.excelforum.com/member.php...o&userid=34918
View this thread:
http://www.excelforum.com/showthread...hreadid=546565

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

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?


"EJS" wrote in message
...

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


--
EJS
------------------------------------------------------------------------
EJS's Profile:
http://www.excelforum.com/member.php...o&userid=34918
View this thread: http://www.excelforum.com/showthread...hreadid=546565

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("A1:D100")


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=546565

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.


--
EJS
------------------------------------------------------------------------
EJS's Profile: http://www.excelforum.com/member.php...o&userid=34918
View this thread: http://www.excelforum.com/showthread...hreadid=546565