Cell Formating in VBA

sampson32

Active Member
Joined
Jul 16, 2003
Messages
312
I have a spreadsheet that I want to make the value turn red when it meets certain criteria

This actually changes the format of the particular cell with condition A

Condition A

If .Value “?whatever?” Then

.Font.ColorIndex = 3

I can turn the formatting back to black (Default - automatic) once the cell is “null” using condition B

Condition B

If .Value = “” Then

.Font.ColorIndex = 1


Once condition A has been met and the formatting has been changed

If the user deletes the contents of the cell with the delete key it works as needed

If the user backspaces the value from the cell and moves from the cell and then back again it works as needed

But if the user backspaces the value from the cell and retypes a value without leaving the cell the code never see the cell as null.

Is there a way around this or is this a black hole?

Vinnie
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

sampson32

Active Member
Joined
Jul 16, 2003
Messages
312
I wanted to stay in vba - I'll just go back to coloring the cell - I just thought that it would be easier on the eyes.

Vinnie
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
No - when you're in the cell, no event takes place.

I seem to remember maybe API code that would monitor in-cell, but can't find it and do not know how to do it myself.
 

sampson32

Active Member
Joined
Jul 16, 2003
Messages
312
You might be misunderstanding me – when I say color the cell I mean use the Interior.ColorIndex =

Regardless, I figured it out- the text change in condition A changes the color of the value and instead of changing the color back to default on null I just change it back with the opposite of condition A – the color changes back when I exit the cell. Kind of like a if Then / ElseIf. It worked OK. It's so simple I don’t know why I didn’t see it earlier.

Vinnie
 

Watch MrExcel Video

Forum statistics

Threads
1,118,730
Messages
5,573,870
Members
412,555
Latest member
mark84
Top