Unhiding individual cells in real-time

[yavshalumov]

Currently I have a macro that goes line by line and determines if a specific cells's content needs to be hidden based on if certain cells in the spreadsheet are null. The macro works well but I would to see if the reverse could be checked in real-time. For instance, at the moment cell "I10" is hidden if columns B through D are null, but if for instance I fill in columns B through D and they are no longer null, is there a way for cell "I10" to no longer be hidden and display the content it holds in-real time.

my code to hide the cell is as follows:
If (IsEmpty(ActiveSheet.Range("B" & RowValuePM).Value) = True Or IsEmpty(ActiveSheet.Range("C" & RowValuePM).Value) = True Or IsEmpty(ActiveSheet.Range("D" & RowValuePM).Value) = True Or IsEmpty(ActiveSheet.Range("E" & RowValuePM).Value) = True Or IsEmpty(ActiveSheet.Range("F" & RowValuePM).Value) = True Or IsEmpty(ActiveSheet.Range("G" & RowValuePM).Value) = True Or IsEmpty(ActiveSheet.Range("H" & RowValuePM).Value) = True) Then
Range("I" & RowValuePM).Select
Selection.NumberFormat = ";;;"
This is inside a loop with RowValue being the current row in the loop.
The contents of "Ix" is formula that adds two dates together.

Is there perhaps a better way to go about this then writing a macro?
thanks,

 

[BThiede]

Not sure what your requirements are, but you can use conditional formatting and just set the font color to white (or whatever the background is) when you have a null.

Brendon

Example formatting formula:

=OR(LEN($B2)=0,LEN($C2)=0,LEN($D2)=0,LEN($E2)=0,LEN($F2)=0,LEN($G2)=0,LEN($H2)=0)

 

[yavshalumov]

Not sure what your requirements are, but you can use conditional formatting and just set the font color to white (or whatever the background is) when you have a null.

Brendon

Example formatting formula:

=OR(LEN($B2)=0,LEN($C2)=0,LEN($D2)=0,LEN($E2)=0,LEN($F2)=0,LEN($G2)=0,LEN($H2)=0)

If I try using this formula, will this not over write my existing formula that adds dates together?

 

[BThiede]

The formula that I gave you goes in to the Format-->Conditional Formatting formula area. You would click a cell in row 2 that should be hidden, then go to Format-->Conditional Formatting. Change the first drop down under Condition 1 to Formula is and then paste the formula:

=OR(LEN($B2)=0,LEN($C2)=0,LEN($D2)=0,LEN($E2)=0,LEN($F2)=0,LEN($G2)=0,LEN($H2)=0)

into the area to the right of that. Now click "Font..." and on the font tab open up the Color: dropdown and click on White (FYI, it looks white to start with, but that means it's not set yet). Click OK in the Format form and then click OK on the Conditional Formatting form. Now you can copy the cell that you set the format for, select all the cells that should be traeted this way, and do PasteSpecial and choose just formats. At this point if any value in columns B through H are blank then the cell will use a font color of white, which will make it seem empty for the most part.

Good luck,
Brendon

 

[yavshalumov]

Brandon Thank you for your help, the conditional statement worked perfectly.

I had a related question dealing with conditional statements. I wanted to created a second conditional statement but for some reason it does not approve my syntax. Could someone please show me what i am doing wrong.

= $I4<$A$54 AND (OR($J4 = "In Progress", $J4 = "Open", $J4 = "Past Due"))

I want to say if the cell value of I4 < A54 and J4 is either of the three values. The statement works fine without the OR statement along side. I cannot figure out what is wrong with my or statement.

 

[yavshalumov]

I figured out what was wrong with my syntax.

formula is: = AND($I12<$A$54,(OR($J12 = "In Progress", $J12 = "Open", $J12 = "Past Due")))

thanks for all the help,

Yasha