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

[LtCmdrData]

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!

 

[Joe4]

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:

=$G2=""

 

[LtCmdrData]

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]

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]

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]

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]

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]

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)?

 

[My Aswer Is This]

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

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]

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.