Cell Formating in VBA

sampson32

Active Member
Joined
Jul 16, 2003
Messages
312
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top