Solved How to change the color of a cell on excel

January 13, 2018 at 22:22:28
Specs: Windows 7
Can you help me out how to change the colors of the cell.
if The cell H value is left or vacation the cell A & B will be green and if its final settlement then it should be orange.
Regards
Diwas

See More: How to change the color of a cell on excel

Report •

✔ Best Answer
January 16, 2018 at 07:45:15
I already provided the solution. Conditional Formatting is all that is required to accomplish your goal. Did you try it?

Follow these steps:

1 - Select columns A & B (or the just the range of cells in columns A & B that you want to color)
2 - Open the Conditional Formatting utility
3 - Choose "Use a formula to determine which cells to format"
4 - In the box that says "Format values where this formula is true:" Enter the following formula, where the x is the first Row of the range in which you want this formatting to occur. (x=1 for the entire column, or e.g. x=4 if H4 is the first row in your range.

=OR($Hx="Vacation",$Hx="Left")

5 - Click the Format box, then Fill tab, then choose the color Green
6 - Click OK twice to back out.

Now chose Vacation or Left from any of your Drop downs e.g. H7, and A7:B7 will turn Green.

7 - With the same A:B range selected, go back into Conditional Formatting and choose Manage Rules
8 - Select New Rule
9 - Choose "Use a formula to determine which cells to format"
10 - Enter this formula, replacing the x as before

=$Hx="Final Settlement"

11 - Format button, Fill Tab, Orange, OK, OK

Now chose Final Settlement from any of your Drop Downs e.g. H7, and A7:B7 will turn Orange.

If you choose Working, the cells in A:B should turn/remain uncolored.

I don't know what you mean by this:

"Rest of the cell won't be colored until i won't change their values."


message edited by DerbyDad03



#1
January 14, 2018 at 08:45:35
Basically, you want to use a variation of Tip # 3 shown here:

https://www.techrepublic.com/blog/1...

Instead of selecting the entire data set, you only want to select Column A & B and then use this formula for the Conditional Formatting:

=OR($H1="vacation",$H1="left")

You will need to set up a separate Rule for "final settlement".

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
January 14, 2018 at 08:53:00
Thank you for the help.
Can i get a vba code instead of conditional formatting.

Report •

#3
January 14, 2018 at 12:37:33
Why? Why not use the simplest method available?

message edited by DerbyDad03


Report •

Related Solutions

#4
January 14, 2018 at 21:59:01
Thanks for the concern but to be clear kindly seen below
For Example
I have 4 selection on coloumn H which includes Working, Left, Vacation, & Final Settelment. I need to Color cell column from A TO B if i select Left, Vacation or Final Settlement. Can you help me on this.
if The cell H value is left or vacation the cell A & B will be green and if its final settlement then it should be orange.
Let suppose i made Cell Value H4 = "Left" Then automatically the cell A4 & B4 should be Green Color. Same like if i made Cell Value H7 = "Final Settlement" Then automatically the cell A7 & B7 should be orange.
Rest of the cell won't be colored until i won't change their values.

Regards
Diwas


Report •

#5
January 16, 2018 at 07:45:15
✔ Best Answer
I already provided the solution. Conditional Formatting is all that is required to accomplish your goal. Did you try it?

Follow these steps:

1 - Select columns A & B (or the just the range of cells in columns A & B that you want to color)
2 - Open the Conditional Formatting utility
3 - Choose "Use a formula to determine which cells to format"
4 - In the box that says "Format values where this formula is true:" Enter the following formula, where the x is the first Row of the range in which you want this formatting to occur. (x=1 for the entire column, or e.g. x=4 if H4 is the first row in your range.

=OR($Hx="Vacation",$Hx="Left")

5 - Click the Format box, then Fill tab, then choose the color Green
6 - Click OK twice to back out.

Now chose Vacation or Left from any of your Drop downs e.g. H7, and A7:B7 will turn Green.

7 - With the same A:B range selected, go back into Conditional Formatting and choose Manage Rules
8 - Select New Rule
9 - Choose "Use a formula to determine which cells to format"
10 - Enter this formula, replacing the x as before

=$Hx="Final Settlement"

11 - Format button, Fill Tab, Orange, OK, OK

Now chose Final Settlement from any of your Drop Downs e.g. H7, and A7:B7 will turn Orange.

If you choose Working, the cells in A:B should turn/remain uncolored.

I don't know what you mean by this:

"Rest of the cell won't be colored until i won't change their values."


message edited by DerbyDad03


Report •

Ask Question