Hide cells based on value of another cell

[Kmahraz]

Hi Ninjas,
Not sure if this is possible or not; but I would like to know if I can hide certain cell based on the value of a different cell using VBA.
If J22 = 0 hide value or entire cell of F34, 35, 36, 37 and J34
If J23 = 0 hide value or entire cell of C34, 35, 36, 37 and I34


Thank you!
Karim

 

[oldchippy]

How about this way?
http://www.pcreview.co.uk/threads/hide-cell-values-based-on-a-condition-in-another-cell.2947547/

 

[Kmahraz]

Hi OldChippy,
Thank you for the proposed solution, unfortunately I have formulas incorporated into those cells, so can use what's listed in the shared link.
I believe a VBA solution is the way to go.
Thanks
K

 

[Kmahraz]

Hi @Luke M , is this even feasible?

 

[Luke M]

Let's clarify what you mean by "hide". Using the built in items, we could set up code to hide rows 34:37, or we could hide columns C:F. With the placement of your data, I would suggest the former. Just want to make sure it's ok to hide the entire row.

Next question, is the value in J22 and J23 the result of a formula, or a manually entered number?

Finally, I might suggest that if one or the other sets of numbers is to be displayed, just move the raw data somewhere else (a hidden sheet) and use formulas to get the correct values into a visible cell. (idea in attached file)

 

[Kmahraz]

Hi Luke,
Thank you so much for taking the time.
Unfortunately, I can't hide the entire row, currently my report has certain layout and it's difficult to make change.

The value of J22 and J23 is the result of a formula.

 

[Hui]

How about setting the cells text color the same as the background color using a Conditional Format?

Select cells of F34, 35, 36, 37 and J34
Goto Conditional Formatting, New CF
Use a Formula
=$J$22=0
select the format and set the Cells Text Color to be the same as the background color

Repeat for the other range

 

[Kmahraz]

Hi Hui,
Thank you so much for the suggestion, I will give it a try and keep you guys posted.
The other option I was thinking is to have maybe a picture or a an object that will appear or disappear on the top on those cells once the value j22 or j20 is equal to 0.
Thoughts?
Regards,
K

 

[Kmahraz]

Hui,
Tried your suggestion and it worked, I will run this idea by my team and see what they think.
Thanks again for your help!

Regards,

K

 

[Stevie]

You could also place a white shape over the desired location and make it visible/not visible using vba based on the required cell's value.
Personally, I think Hui's method is cleaner, and if it does what is required, I would go with that.

 

[Kmahraz]

Thanks Stevie for the feedback, I went with Hui's suggestion