Is there a way to protect the conditional formatting of accessible cells in a protected sheet.
Detail: I have a protected excel sheet. However some of the cells are made accessible so others (my secretary) can fill them up. The accessible cells have conditional formatting which become nonfunctional during pasting from other sources if "special paste" is not used.
I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
View 3 Replies View RelatedExcel 2007 Conditional Formatting. I am trying to apply conditional formatting to a wide range of cells. I only want the rule to adjust the color and shading, under the FONT tab in the section below. Specifically, I do NOT want to adjust the number format (I have the user select the scaling and use VB to populate the number formatting). I accomplish this by removing the number formatting by going into the conditional formatting rule, clicking on format, and then on the first tab (Number) -- on the bottom right there is button to Clear.
This resolves the issue perfectly, and I can operate the report exactly as I would expect.
Unfortunately, when closing the report (after a save), and then re-opening, the conditional formatting number format is no longer cleared. Worse yet, because it no longer is cleared, the scaling VB doesn't work, unless I go back into conditional formatting rules, and go to the number tab, and click clear.
Screen shot of after re-opening, no changes:
Report looks terrible (not real data):
Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A
Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).
there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.
I'm using 2007 and want to add conditional formatting to multiple cells.
Say I have 3 columns and 10 rows of data (A1:10 ; B1:10 ; C1:10). I want to add an icon set to everything in columns B and C based on a comparison with the figure on their immeidate left. So B1 is compared to A1, C1 to B1 etc.
When I do it though it compares everything to A1 (formula in the Conditional Formating is =$A$1 and it won't let me remove the $'s). It looks to me like I can't do it and have to add the formatting to every cell indiviually (which is quite a lot).
I have two conditions for a cell. One is that it counts the time elapsed in cell M4, (=now()-J4). The other is whether or not we are still waiting for a response (in cell L4, Y or N). What I would like to do is make it so that when we are still waiting for a response the M4 cell is highlighted yellow if more than 3 days and red if more than 6 days, but if cell L4 is changed to "N", then the formatting goes away. II use Excel 2010.
View 1 Replies View RelatedIs it possible to set a conditional format for the following scenario:
a user clicks on or tabs over to A1 making A1 the "active cell". Once the cell is "active", a message appears in A2?
(Excel 2007)
I tried opening an excel 2003 file in an xp environment and it says that the file is not accesible? how can i open the file?
View 3 Replies View Relatedsee spreadsheet. columns a-f contain pupils details.g4-r23 contain marks for 12 questions. row 24 contains total marks for that question. questions are out of 2,3,4 and 5 marks. I would like to format the cells to reflect % for that question. At the moment I have to format column by column to do that. When I format the whole spreadsheet 0 and 1 show in red 2 and 3 in yellow and 4 and 5 in red. I want 2 when equalling 100% to show green not yellow.
View 9 Replies View Relatedi have a spreadsheet in excel 2007. It shows a students target grade in one column and their recent test mark in another column. Firstly i have applied conditional formatting to say whether or not the student has hit their target, below or above, using red, yellow and green colours. This all works fine.
Now i would like to add a formula that counts the number of cells that are red, yellow or green etc.....
I have a workbook containing two worksheets of staff training records.
The first work sheet contains a list of names and the dates they completed various training courses. I have used some simple date based formulas and conditional formatting to colour-code their name depending on whether their earliest retraining due date has passed, is coming up in the next few weeks, or is a long way off. The data is set out alphabetically, one person per row of data.
The second sheet contains the same list of names, but each person's data is split across two consecutive rows. The cells in column A which contain the staff names are merged in pairs so that the name heads both rows of data.
I want the colour coding of the merged name cells in sheet 2 to automatically copy the colour coding applied to the single name cell in sheet 1, but don't know how.
I'm using excel 2010.
I am applying conditional formatting on a range of cells. I have 2 conditions to color cells in different colors. Once I enter data, cells get colored corectly according to conditions. What I want is to count how many cells are colored by conditional formatting.
I tried using VBA --- Evaluate formula for each cell and count if TRUE.
I observed, for Excel 2007, as the condition is applied on the range, I expect the formula to be changed dynamically for each cell with appropriate addresses, like for A1, if am referring to cell C1, for A2 it should automatically refer to C2.
And excel takes care of this cells are colored correctly,however, when I try to check the 'FormatCondition' in VBA, I get only one formula for entire range. SO I can not use evaluate to find if the condition is satisfied or not ?
I'm using Excel 2010.
I have created a spreadsheet with 3 month inspection schedules on using the formula below.
=DATE(YEAR(C50),MONTH(C50)+3,DAY(C50))
I then copy this in to a cell where I want the date to appear... and then in to another cell where I want a further 3 month date to appear. Obviously changing the cell reference.
I would like the formula to ignore the very first cell "C50" if there is no date inputted in this cell.
i want to improve some of my application by testing if a folder is accessible to the user... i've got some code to check if a file is already is use which is useful... but before that we have folders in work that are only accessible to certain users for reporting and if the person doesn't have access to it within a macro it Errors out... i'd like some kind of macro to display a message box like windows does... Directory is not accessible, Access Violation... it has to check directly for the Accessible part and not just an error...
View 2 Replies View RelatedI have designed a live calendar and am trying to highlight selected dates from a column in the calendar. I am able to select single cell in the column and highlight the date in the calendar, But am unable to select multiple dates in the column and highlight multiple dates in the calendar using conditional formatting.
Eg: calender dates are from B5:H9 and multiple dates are in column J10:J13
J column
--------
J10 7/10/2013
J11 7/20/2013
j14 7/29/2013
How to sort out a way to count cells whose fill colors have been set via conditional formatting.
I'm sure that I was once able to use Chip Pearson's CountOfCF function back in the days of Excel 2003, as it worked around the 3 condition limit and used .ColorIndex which assumed that you'd only ever be formatting using the .ColorIndex values of 1 to 56, but I can't seem to be able to get it to work in 2010.
I am using Excel 2010 and I have a password protected workbook with password protected sheets that uses several macros. Most of them, in order to run, have to un-protect the sheet and then re-protect it again. This has been accomplished easily enough by adding ActiveSheet.Unprotect Password:= "mypassword" and ActiveSheet.Protect Password:= "mypassword" to the appropriate places in the script. All of my macros, which do various things like sorting and moving data, deleting blank rows, displaying dialog boxes containing warning messages etc. run fine.
My problem is this: when I password protect the sheets manually, I have checked the following options in the "Protect Sheet" dialog box. Under "Allow users of this worksheet to" I have checked 1)Select unlocked cells and 2)Format cells. After entering my password and closing the dialog box my sheet is protected, but I can edit cells in the manner my allowances permit. However, once I run any of the macros that un-protect and re-protect the sheet, I remain able to select and edit unlocked cells (practically, for my purposes, this means that I can input data which will appear in the default font size and color of the sheet) but I cannot format cells (which, practically, for my purposes would allow me to occasionally change the font color and size of the data). Naturally, after running a macro, the other cell-formatting options are unavailable to me as well. Is there any way to get my manual selections to remain in place after running a macro that functions as mine do? Or is there any way to make my manual selections the default settings for a protected sheet?
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
Excel 2002
I have a simple spreadsheet and am trying to get some Conditional Formatting to work.
Cell1 = Name
Cell2 = 1
Cell3 = 2
Cell4 = 3
If cells 2, 3, and 4 all match I want the background in cell 1 to change to green.
I've tried (Formula Is =B1=C1=D1 green) with no luck.
I'm using Excel 2007. I would prefer to stay away from the scripting side of the house if possible. This is basically a 3 day forcast weather chart. The top is the actual weather data, the bottom portion is a color coded reflection of how the weather affects various things.
This product is created in excel, but will be embedded into a powerpoint. It will be updated daily. Here is what I would like. I want the color chart at the bottom to update automatically based on the data I enter above. I have a grasp that I can update the color through conditional formatting, although im not exactly sure what that will look like with all of those cells.
I also figured out that I can insert the letters in those lower cells with something similar to " =IF(C6>90, "T", "") " which would put in a 'T' for Temperature when the temperature got above a certain degree.
I run into a problem when I have multiple factors affecting a single cell. For instance on the example in day 2 of my image. Personnel are affected by Temperate AND UV Index. How would I set up that cell to pull that information from both of those cells and display it accordingly? I would prefer the letters to stay separated by the comma, but I could live without that. The default cell color will be green, with the potential to be yellow or red. I left a few examples of possible situations on day 2 and 3.
I am gaining an error in my code due to the sheet being protected.
I cannot seem to get the correct code to allow for the code to still run, while the sheet is protected.
[Code] .....
I tried protecting the sheet via:
[Code] ....
But I still get an error.
Is it possible to protect an inserted text box from being moved, stretched, etc. while allowing data input into the cell/s? I'm using a 2003 version of excel.
I've attached the sheet in question. The yellow text boxes are what I want to lock, yet still allow data input.
excel tip example.xls
I build a lot of spreadsheets at work that I email out and the person I send them too has to enter information in the cells highlighted in yellow. Since not many of the end users are too computer savvy I lock and protect all the cells that they shouldn't be changing.
This can take forever, is there any way to have all the cells in a worksheet become locked and protected based on the formatting they have? All cells that aren't yellow?
I've got a workbook where I need to protect certain cells by locking them.
The trouble is when I do this I lose the ability to apply formatting and to insert symbols in other cells?
I've checked the obvious options for when you're locking cells but nothing seems to be stopping this there.
Is there a reason it disables these options, a work around or am I just being dumb?
I'm using Excel 2007 by the way.
I've got a spreadsheet that has password protection before you can "modify" it. All of a sudden today, when I open the spreadsheet and enter the correct password, it still opens but only as Read Only.
View 3 Replies View RelatedI have a file used by our employees through our local area network. Because that file has many functions and possibilities that took us a lot of time to create, I would like to protect this file so no employee can copy it to an external device without a password. It would be great, though, if the file could be exchanged or sent among employees as logn as it remains WITHIN our internal network.
Is it technically possible to protect an Excel file in a such a way?
S/W Environment: Excel 2010
Some of my worksheets are permission protected. They will allow me only to view the sheets - No editing, copy, cut, sort, add filter, etc. Is there a way to hack this and copy data (including formating) into other / new worksheets?
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
It is possible to use conditional formatting using 2 differents file. For example I want to check one column in another file, if 100% then one column with another file will be shaded into red. Even manually or using vba.
View 4 Replies View RelatedI need to change VBA to Password Protect all sheets to allow user to select protected. I have:
View 2 Replies View Related