• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a formula to make subsequent cells in a row must be turned out GREY

B

breddi76

New Member
Hi,


I have a scenario where i ahve TRACK A and TRACK B in excel sheet. if i select TRACK B in a cell C3 and we want adjacent cells (D3, E3, F3...etc.) must be turned in to grey i.e not available to access these cell. but if i select TRACK A these cells must be accessible and be able to enter data.


Any help is highly appreciated.

Thanks in advance
 
Need to do 2 things to meet your goal. The first is coloring the cells Gray. We'll do this via Conditional Format.

Select cells D3:F3, condition format, formula is:

=$C$3="TRACK B")

Set format to gray fill, ok out.


Now, to disable entry into these cells. With D3:F3 still selected, we're going to go into Data Validation, custom rule of:

=$C$3="TRACK A"

Uncheck the box to "ignore blank"

Ok out.


All done. =)
 
How can I apply this concept to all rows ex: from C3 to C70. When I drag from C3 to C70 it did not give me the intended result.


Thanks in advance.
 
Remove the dollar sign from in front of all the row numbers, e.g.

=$C3="TRACK B")


and


=$C3="TRACK A"


The dollar signs tell XL that you are using an absolute reference. With it removed, the row reference will change as you move to a different row.
 
Thanks for the help Luke. However, this formula applies to only single cell. If select multiple cells for this formula it did not work.


If Track A is select a cell X10 then Y10 to AB10 must be active. and if TRACK B is selected for a cell X11 then Y11 to AB11 must be inactive and gryed out. I have to apply this concept from cells X10 to X70.I wanna apply this formula to all cells in one shot.

Thanks in advance.
 
Select all the cells you need to make changes to, e.g. X10:AB100. With X10 being the active cell (this is critical), set the conditional format formula to:

=$C3="TRACK B"


and the Data Validation rule to:

=$C3="TRACK A"

with the "ignore blank" option unchecked.
 
You must log in or register to reply here.
Back
Top