PDA

View Full Version : [SOLVED] Delete Cell contents when data is added to another cell within a range



GribbiN
06-25-2015, 05:59 AM
Good Evening,


I recently added an icon set with =sign to my workbook to monitor performance direction.
Now unless every row has data on the data sheet i just get the numbers instead of the icon 1,0,-1
If i add data to every row the icons appear as formatted "Confused"


So i either need some code to help combat the conditional formatting issue so that every row doesn't need data "IF" maybe??
OR
A code to delete cell G1 once data is entered into any cell within H1:DD1 as i have temporally added a blanket number in column G
This code would need to be copied down for 40 rows in total.



Thanks in advance for any help


John

Here is my file, CPH SUMMARY is where i have added the icon set
CPH SF & CPH GEORGE are the data sheets

13784

p45cal
06-25-2015, 06:53 AM
I've looked sat the sheet CPH Summary ranges E3:E42 and H3:H42 and I get icons in the conditional formatting, all of the cells. When I remove data from T and S or V and U I still get icons - no numbers. I'm using Excel 2010. What version are you using?

SamT
06-25-2015, 07:18 AM
Would you please put some code in the Workbook before close event that restores Excel to the state it was in when your workbook is opened.

Lucky for me, I have a sub that Restores all the Built-In ToolBars to their Default state, but I still had to reinstall all my custom tools bars.

I still haven't figured out how to restore the upper right window state buttons and the Close command on the Main Menu. You know, the ones that CLOSE EXCEL!

:banghead:

Since you removed them, tell me how to put them back.

p45cal
06-25-2015, 07:22 AM
I see now, you've seeded column G in CPH SF.
How about changing the formulae? Eg. in E40 of CPH Summary, instead of:
SIGN(T40-S40)
have:
=IFERROR(SIGN(T40-S40),0)

ps. when I remove the seeded 2000 from column G I get #N/A errors ather than 1,0, or -1.

p45cal
06-25-2015, 07:28 AM
Sam, you'll probably begin to do the same as me now, which is never allowing vba to run until I've had a look at the code behind the workbook.
I suspect the command you're looking for might be:
Application.DisplayFullScreen = False
although when I set this property to True on mine I can still see the top right windows state buttons!

The entirety of the code in the workbook is:
Private Sub Workbook_Open()
Application.DisplayFullScreen = True
Sheets("Summary").Select
End Sub

Private Sub Workbook_Close()
Application.DisplayFullScreen = False
End Sub
The last one never gets called because it's not an event, it would do better if were n a Private Sub Workbook_BeforeClose(Cancel As Boolean)

GribbiN
06-25-2015, 07:29 AM
SamT it sets to full screen upon opening, i totally forgot about it. Right click and close full screen. Its only a viewing mode :( sorry.

p45cal ill try that now, i get #N/A for errors but the fields with data go to there numbers. See pic, i would have thought if it reckonises the data then it should add the icon. Icons only appear to work if the full loop is correct.

13785

SamT
06-25-2015, 07:32 AM
Thank you. :thumb :beerchug:

GribbiN
06-25-2015, 07:34 AM
Works perfect!!

Thanks a lot

SamT sorry for the noob mistake