Conditional Format Shading Stop Updating Until Save...?
Aug 17, 2009
I have a sheet that highlight cells to show that they need to still be filled out if another cell in the same row has a number in it. What happens is that when you put the number in the row the cells won't become shaded or partially shade until the workbook is saved.. Maybe I can put some code in that tells the work book to update whenever a cell is changed?
Sheet 1 and Sheet 1a 1a is used to provide a start and finish date for a task - there are many of them The following is located in a cell in sheet 1 which looks at 1a for the dates etc
The end result is an X located in the respective feilds. This works fine but. I have set up the Calendar area in 1 so that when the X is shown it is Red. The sheet also shows a Blank in both the SS [Sat and Sun]
What I would like to do is to have instead of the blank in the SS [Sat & Sun] a shaded grey which would clearly indicate that this was weekend.
What I am after is the weekdays with the X as now and the weekend only in a grey shade as it is at the moment without the X
I have a Sheet ( Named "Summary" for Example ) of about 4,000 Rows that has a LOT of Conditional Formatting.
I Added Another 100 Rows this Morning and when I Tried Saving it a Message Saying that Not All the Formatting for the New Data that had Been Added had Been Saved. Is there a Macro or Something I can Run that will Make the Conditionally Formatted Cells Stay the Colour that they are but Delete the Conditional Formatting Part of it Achieved Using "Format" & "Conditional Format" from the Menu Please.
Ideally I would like to be Able to Enter the Number of Rows ( From Row ? to Row ? ) that I want this to Apply to.
I'm trying to use the following formula in conditional formatting in cell A2 when the range A2:AW64 is selected (this conditional formatting will be copied to all cells in the selected range):
Formula is: =isodd($A2)
The error I keep getting is this:
"You may not use references to other worksheets or other workbooks for Conditional Formatting criteria."
I'm trying to use this conditional formatting because column A is our "Order" column. "Order" can be from 1 to 13 and this column is sorted ascending. There can be multiple rows in each Order number. So I want to shade all 1's, 3's, 5's, etc... so that each Order group has alternating shading.
I also want to use conditional formatting, not VBA, to get this to work. In the future, rows might be added to an Order number or two and I want the shading to automatically adjust.
In my worksheet i use a macro to produce my results which runs perfect.
Later i copied my range and pasted as picture link in another sheet.Since then the macro takes a long time to run. I suppose while running the macro xl continuously updates the picture slowing the macro.
I use the following code to insert and delete a background picture upon opening and closing my workbook, but everytime it closes it asks me if I would like to save it even if I've already saved it just before closing. It does this because after the background is removed it senses a change. I would like to get this to stop, is it possible??
Private Sub Workbook_Open() Sheets("Instructions").Select Range("A1").Select Sheets("instructions").SetBackgroundPicture Filename:= _ "H:Chiefmate2nd MATE FILETIDES & CURRENTS Formsdontdelete.gif" userform1.Show Sheets("current inputs").SetBackgroundPicture Filename:= _ "H:Chiefmate2nd MATE FILETIDES & CURRENTS Formsdontdelete.gif" End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Sheets("instructions").SetBackgroundPicture Filename:="" Sheets("current inputs").SetBackgroundPicture Filename:="" End Sub
I have a spreadsheet that has several formulas that update once all the values are inputted however I noticed that if I drag the formulas down the column, the cells does not update until I click the save button.
I have a an Excel workbook (wb1) with 4 worksheets. I also have a personal.xls that contains 4 worksheets. wb1 has volatile functions.
I have calenders, dropdown lists, read from a file that is closed, etc.. When I open wb1, look at it (I do not make any changes by hand) then close it, it gives me the " Do you want to save?" prompt.
I have tried numerous codes to stop it and I can not get it. I need it to give me the prompt if I have made a change and not give me the prompt if it is just a change from the volatile functions.
I cannot just tell it to save in the background because my boss does not want the date changed if it is just looked at.
The user opens numerous files when searching for a file that is almost what he needs, then he saves it with a new name and goes from there. Or he just opens numerous files to get ideas on how to make the new file.
The user cannot have to do anything to make it work. I know that the code will need to be copied to any existing workbooks. I have the code to do that. That would be done as the files are created to new ones. Also when he is starting from scratch the code would be in the template he uses. Eventually all of the files would be converted
Private Sub workbook_Open( ) Me.Saved = True End Sub
Private Sub Workbook_Open() ThisWorkbook.Saved = True End Sub
I have a spreadsheet that has hyperlinks in it and is mostly protected to prevent changes. Is there a way to prevent the "save changes" dialog box from appearing when this file is closed as I have to make this file available to others and don't want them to get this message or make save any changes.
I have to send some data on hourly basis i have a macro that extracts the data from the master sheet and creates a new workbook for the same and saves the same. Problem that i am facing is that i need to save the file with the same name. and on doing that it shows a pop up that do you want to replace existing file. Is it possible to stop this pop up. Code that i am using to save the workbook:
ActiveWorkbook.SaveAs Filename:= _ "C:Documents and SettingsAmritDesktop Training Scedule For Next Hour.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False, ConflictResolution:=xlLocalSessionChanges
I do have an excel file that I do not want to be moved to another location. I was wondering if it is possible to hardcode a location for the file and then check its current location in the drive and if both locations does not match delete its contents.
I tried something like below but I keep getting an obeject required error. I would really appreciate if someone can guide me to the right direction.
Private Sub Workbook_Activate()
Dim location location = "C:InetpubwwwrootLeadershipJC est.xls"
Dim currentloc currentloc = ActiveWorkbook.FullName
If location = currentloc Then MsgBox "Due to security settings of this file you can not save this file." Else Sheets("JobCosting"). Range(Selection, A2.SpecialCells(xlLastCell)).Delete End If End Sub
How can I update the formula of conditional formatting among many cells. "Applies to" lets me choose many cells but the actual formula locks into one cell (i.e. $A$1) but doesn't update across the other cells (i.e. A2, A3, A4).
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
The code below is for a macro that allows the user to create a list of contacts. The column containing the names of these contacts are then referenced by a userform (code not present) by way of a Named Range. I want to update the named range whenever the user adds another contact so this new contact shows up in the user form.
I am currently referencing the range containing the names using R1C1 style, but I cannot get the variable aspect to work correctly.
i want to type 1-1 in my excel sheet and it is automatically coverting to 1-Jan and then i checked for formatting to the way i want then i founf excel automatically selecting custom option in format then if i select general my 1-jan is converting to 41640 number. Now i want excel sheet should show what typed in it i .e. 1-1 only.
When I remove "Release date:" from "Release date:24 December 2008," excel will automatically change the text to "24-Dec-08." Yes, I did try to format the column to "text" before using find and replace.
This wouldn't be a problem, except that half the dates are in Dutch, so only half the dates are changed, which means that I can't make all date formats in a column uniform.
Right, sorry this is probably a simple one but I can't do it... I have information in a variable that is "1-4" the variable is defined as a String but whenever I use the following code excel turns it into a date.
I have five fields that have a conditional format applied to them: (see attached). Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
I have a range which has conditional formats based on other cells. I want to copy this range into another sheet and retain the current formats as fixed formats without copying the conditional formulae. ideally in VBA.
How can you use VBA code to turn off the annoying "Stop if True" default setting when doing conditional formatting in excel 2007? Right now when you have multiple conditional formatting conditions in a cell, it stops checking and formatting other conditions if one of those conditions is satisfied. I don't why they set this as the default...
I have conditional formatting rules set up using excel 2003. The file is opened in 2007 with the above-mentioned default setting that screws up all my conditional rules.
I have conditional formatting set up so that the cell becomes highlighted if it contains a specific text.
Example of wanted conditional formatting:
Conditional formatting rules manager Rule
Stop if true
For some reason this formatting is inherited by another adjacent cell as we continue to input information.
Column M is formatted so if 'AP' is placed in any cell in that column the cell fills pink. As more information is inputted into the sheet, the conditional formatting copies to Column N. It does not happen with every entry and I have not been able to isolate the specific steps to recreate the copying. Multiple people use the same sheet and fill it out and needed.
After working with the sheet changes are made to the "applies to" column without people meaning to.
Conditional formatting rules manager Rule
I would like to make this formatting so that is only applies to the Column M and not "travel" to other cells of the sheet.