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


"EJS" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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




In article <[email protected]>,
EJS <[email protected]> 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

[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.

Please Login or Register  to view this content.

Calling the Macro:
HighlightCells Range("A1:D100")


Sincerely,
Leith Ross

[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?


"EJS" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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.