EXCEL - Black background switches to white background & black text when entering data

Two Rows
Four columns

ALL rows are black backgrounds to start
Upper row is labels, white text.  So:
Year  Make  Model  Warranty  

Lower row is just solid black

In looking at this you'd see two rows.  Upper row is all black background with white text.  Lower row is just solid black.

Is there any way to make the lower rows switch formatting with data?  So that, once data is entered, the cells backgrounds go to white and the text goes to black.  Put another way - when there's nothing in the lower row, it's just all black.  As soon as I start to enter data there, then the cell's background changes and text color changes.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


you have to enter the data first and validate it with enter or tab or arrow

pls place in the sheet module this code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2:D2"), Target) Is Nothing Then
        If Target.Value <> "" Then
            Target.Interior.Color = vbWhite
            Target.Font.Color = vbBlack
            Target.Interior.Color = vbBlack
        End If
    End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCost AccountantCommented:
Use conditional formatting to format based upon formula:


When the cell is filled in it will then stop being black.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Right click on Sheet Tab --> View Code --> Paste the following code into the opened code window --> Save your workbook as Macro-Enabled Workbook --> Close the VBA Editor.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A2:D2")) Is Nothing Then
    If Target <> "" Then
        Target.Interior.ColorIndex = xlNone
        Target.Font.ColorIndex = xlAutomatic
        Target.Interior.ColorIndex = 1
        Target.Font.ColorIndex = xlNone
    End If
End If
End Sub

Open in new window

For details refer to the attached sheet.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

oaktreesAuthor Commented:
Hello RGonzo1971 & sktneer!

Seems like when I use the code it disables crtl+z?  So that, if I want to undo something it doesn't work.

What do you think?


Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You cannot use Excel inbuilt feature Undo to undo the changes applied through a VBA code.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.