Hide cell contents using VBA

Hi experts,


I am trying to write a vba code to hide contents of few cells in the worksheet.

I have the cell address that needs the modification in another cell.

The code should take the cell number and hide the contents of the text that is being displayed in another cell.

For example:

C2=D98

D98= Marketing

I would like to see the Marketing text to be hidden/white text.

Regards,
Wunder

Originally Posted by nigelbloomy

Try this:

ActiveSheet.Range("D98").Font.Color = vbWhite

Hmm

Thanks Nigel.

But it has to reference the cell C2 in the sheet first. d98 is not static and keeps changing depending on the user.

Originally Posted by nigelbloomy

Sorry. I didn't understand the first time. This should work then.

Sub test()
stValue = Range("C2").Formula
stValue = Right(stValue, Len(stValue) - 1)
ActiveSheet.Range(stValue).Font.Color = vbWhite
End Sub

Hi Nigel,

Thanks again.

I get a run time error '1004'

When I use the above code.

It points to

ActiveSheet.Range(stValue).Font.Color = vbWhite

Does range take stValue?

Hmm changed it and it still shows the error.

Originally Posted by nigelbloomy

It works for the example you posted. What is the actual formula in C2? This code can only handle a formula like =D98. The code looks at the formula in C2 and takes off the = sign and then gives that to the range to turn white. If the formula has more in it like =if(D98="Marketing,"Marketing,""), then we need to use a different method to find the cell reference in there.

Hi Nigel,

Thanks.

The cell C2 just has D98 and not =D98.

The cell C2 tells the code to modify the cell that needs to be hidden.

Thanks.

Works a treat.