VBA Code to Fill Blank Cell with Color based on Another Cell's Contents

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
36
Hello,

I have a column with data and I have the cells highlighted based on the contents using Conditional Formatting. If it says "Frozen" or "Refrigerated" it highlights blue. If it is "Dry" then it highlights green. If it is blank then no highlight. I am trying to write some VBA code to fill the same row cell seven columns to the left with the same blue, green, or no fill. Can anyone help me with this code? Thanks!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
I am trying to write some VBA code to fill the same row cell seven columns to the left with the same blue, green, or no fill. Can anyone help me with this code?
Why do you need VBA code to do this? You can use Conditional Formatting for that too. Conditional Formatting can run off of data in other cells, not just the cell that it is found in. Just use the formula option.

For example, if you wanted to Conditionally Format cell A2 based on whether G2 is empty, you could use the following Conditional Formatting formula in cell A2:
Code:
=$G2=""
 

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
36
I wanted VBA code because this is part of a larger macro. I need to hard code the fill color into the empty cells because later I will type data into the cells and I don't want the color to go away even if the original data in the original column changes. If I could just copy my original column into the second column and then clear the contents but keep the color, that would accomplish my goal. I hope I'm making sense.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
You said:
I have a column with data

What column is this?

And do you want a sheet change event script which will automatically run when you manually enter a value into the column on that row

Or do you want a Macro that runs when you press a button.
And the script would look all down that column
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Since you are including "blank cells" in there, how do you know how many rows to apply this to?
Is there some other column that always has data that we can use to determine where the last row resides (so we know how far down to apply this VBA code to)?
 

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
36
There are other columns in the sheet that always have data so I am using a last row code determine the total number of rows each time.
 

LtCmdrData

New Member
Joined
Jan 24, 2018
Messages
36

ADVERTISEMENT

I want a macro that runs when I push a button and the macro would work down the column. The sheet has other columns of data without any blanks so I'm using some last row code determine how far down the column it needs to go.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,034
Office Version
  1. 365
Platform
  1. Windows
There are other columns in the sheet that always have data so I am using a last row code determine the total number of rows each time.
And what column letter would that be?
What are the two columns that are involved (the one with Conditional Formatting, and the one you want to copy the coloring to)?
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
Well if you want a sheet change event script which runs automatically when you change a value

Use this script. Since you were not specific about What column this script assumes you will entering these values in Column 8

See first line of code where is says 8 change that to what you want.

Now this part of question I did not understand:

If I could just copy my original column into the second column and then clear the contents but keep the color, that would accomplish my goal. I hope I'm making sense.

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
'Modified  2/8/2019  2:01:35 PM  EST
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
With Target
    Select Case Target.Value
        Case "Frozen", "Refrigerated"
            .Interior.Color = vbBlue
            .Offset(, -7).Interior.Color = vbBlue
        Case "Dry"
            .Interior.Color = vbGreen
            .Offset(, -7).Interior.Color = vbGreen
    End Select
End With
End If
End Sub

 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
I see now you want a Macro to run when you press a button

But you still have not said what column to look down for these values Like Dry.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,583
Members
417,223
Latest member
jelena_

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
Top