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

✔ 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 thexis 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

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

Thank you for the help.

Can i get a vba code instead of conditional formatting.

Why? Why not use the simplest method available?

message edited by DerbyDad03

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

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 thexis 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

Ask Your Question

Weekly Poll

Do you think Slack's direct listing "IPO" is a good idea?

Discuss in The Lounge

Poll History