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?
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
Thank you. :thumb :beerchug:
GribbiN
06-25-2015, 07:34 AM
Works perfect!!
Thanks a lot
SamT sorry for the noob mistake
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.