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

LtCmdrData

Board Regular
Joined
Jan 24, 2018
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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=""
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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

 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

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