Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Convert background color to a particular value

0

I'm dealing with some text cells with a yellow background color. I want to refer a particular value to these cells according to their column so that the result appears in a different cell/location, but I don't know how to do that.

I tried conditionI'm dealing with some text cells with a yellow background color. I want to refer a particular value to these cells according to their column so that the result appears in a different location, but I don't know how to do that.

I tried conditional formating if functions and even looked for VBA codes but was not successful.

Any advice?al forbackground colormating if functions and even looked for VBA codes but was not successful.

Any advice?

Answer
Discuss

Discussion

Hope Dons" answer works but if not, I'm not clear what you mean by " I want to refer a particular value to these cells according to their column so that the result appears in a different cell/location,".

Do you want to change the colour of one or more cells in a column dependent on their text (which conditional formatting will do) as your Question title suggests? Or copy the text or colour into another column as Don has shown or do some other thing?

Can you explain and/ or provide a file to illustrate? 
John_Ru (rep: 4312) Nov 13, '20 at 4:30 am
Thank you for addressing the issue. Dons' answer worked very well.
sam (rep: 4) Nov 13, '20 at 7:09 am
Thank you again for your great responding. Now, I'm asking how I can run this code only for those cells that their background colors have changed. 
sam (rep: 4) Dec 3, '20 at 1:19 pm
Add to Discussion

Answers

0
Selected Answer

Have you taken a look at this tutorial? Formulas Based on Cell Color - SUMIFS, IF, COUNTIF, Etc.

Here is some sample code from it that should help. It will output the color of a cell in the next column to the right of the selected cell/column.

Sub ListColors()

For Each c In Selection 

    c.Offset(0, 1).Value = c.Interior.Color 

Next c 

End Sub
Discuss

Discussion

Thank you so much. It works easily.
Your description of the logic behind the function was great as usual. 
sam (rep: 4) Nov 13, '20 at 7:07 am
To be honest, I have taken the great tutorial. Also, I used the code, and it works very well for SELECTED columns. But, I wonder how I can run this code ONLY for cells whose background color has changed instead of running the code for all the selected columns. 
sam (rep: 4) Dec 3, '20 at 1:59 pm
Add to Discussion
0

Steps to change the background color

Start with selecting the cells the background color of which you want to change.

Create a new formatting rule by clicking Conditional Formatting > New Rule… on the Home tab.

In the "New Formatting Rule" dialog window that opens, choose the option "Use a formula to determine which cells to format" and enter the following formula in the "Format values where this formula is true" field: =$C2>4

Instead of C2, you enter a cell that contains the value you want to check in your table and put the number you need instead of 4. And naturally, you can use the less (<) or equality (=) sign so that your formulas will read =$C2<4 and =$C2=4, respectively.

Also, pay attention to the dollar sign $ before the cell's address, you need to use it to keep the column letter the same when the formula gets copied across the row. Actually, it is what does the trick and applies formatting to the whole row based on a value in a given cell.

Click the "Format…" button and switch to Fill tab to choose the background color. If the default colors do not suffice, click the "More Colors…" button to pick the one to your liking, and then click OK twice.

You can also use any other formatting options, such as the font color or cells border on the other tabs of the Format Cells dialog.

The preview of your formatting rule

If this is how you wanted it and you are happy with the color, click OK to see your new formatting in effect.

Now, if the value in the Qty. the column is greater than 4, the entire rows in your Excel table will turn blue.

As you can see, changing the row's color based on a number in a single cell is pretty easy in Excel. Further on, you will find more formula examples and a couple of tips for more complex scenarios.

Regards,

Rick Bale

Discuss
0
  1. Select the cells you want to format. You can select one column, several columns, or the entire table if you want to apply your conditional format to rows.
  2. On the Home tab, in the Styles group, click Conditional formatting > New Rule
  3. In the New Formatting Rule window, select Use a formula to determine which cells to format.
  4. Enter the formula in the corresponding box.
  5. Click the Format… button to choose your custom format.
  6. Switch between the Font, Border, and Fill tabs and play with different options such as font style, pattern color, and fill effects to set up the format that works best for you. If the standard palette does not suffice, click More colors… and choose any RGB or HSL color to your liking.
  7. When done, click the OK button.

Let me know if this works.

Regards,
Jerry M.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login