I attached my spreadsheet. I made a conditional formatting that when Column M is Expired it will highlight the row as red but if it says Pending then it will highlight the row blue. When I try to copy the formula from the 1st rows and drag it down to the below rows. The Status column will say Expired and highlight the entire rows red.
Is there a way to fix this that if valid until column, valid from column and redeemed date is blank then status will just be blank?
I have a spreadsheet that keeps track of staff training. in colum A is the date they completed the training. this training expires in 5 years time. i would like the cell to highlight red when the date has expired. and when i enter the new date in 5 yrs time the cell to return to no fill color.
I am using the coditional formating to highlight whether a date contained in particular cell is within 12 months of todays date or over 12 months in the past.
This is done by placing the =TODAY() in a cell at the top of my sheet and then in another cell, doing a DATEDIF calculation between the two and this works fine. I can then specify in my conditional formating to turn the cell containing the date a funny colour if the cell doing the DATEDIF calc is > or < 12. Simple!
What I can't do is copy this formatting for the other 30 rows of dates I have. When I try and copy them, it references the original cell, not the one for that row. Example: If my date I want to test is in B2 and my DATEDIF clac is in U2, when I copy the coditional format to the new row, say B3, the new rule still references U2, not U3. I understand about dymamic and static references and I have removed the '$' signs from the formula.
I am putting together a worksheet with all my teams tasks. I have columns A:U and Status is column "M". What i want to do is when ever i select Complete from the list in Column "M" i want to the row to turn Green.
I tried the following:
Selected the Range From Conditional Formatting menu selected Manage rules Selected new rule then Use formula to determine which cells to format formula i used =$M$2=complete and selected green from the format menu
BUT nothing is happening. What am I doing wrong.
BTW the Values in Column are Complete,Development, Not Started Research, QA/UAT. If I can conditionally format based on these selections that would be even better.
I have a data with say around 500 rows and want to determine MAX for each row and HIGHLIGHT them, also if data in all cells is zero then it should ignore and highlight none.
I have tried this formula =B2=(MIN(IF($B2:$E2>0,$B2:$E2))).
But this highlights all the zeroes, you can refer attachment for sample..
I am seeking some code that will check that the PC / Laptop has an online ethernet connection via it's NIC (Network interface card). I want it to output to the spread sheet to a Conditional cell (Connected / unconnected).
I have some vba code that currently copies a status table out of a worksheet, into a temp file for an email. The status table in excel has a column D which can have different status. I want to select everything in the table except rows that are 'Descoped'. Currently my code looks like:
Set rng2 = Sheets("Execution Status").Range("B2:F420")
This picks up everything without looking at the status. I want to change it so it doesn't pick up the rows where Column D contains 'Descoped'.
In pseudo code I would describe it as:
Set rng2 = Sheets("Execution Status").Range("B2:F420") where value in D5:D420 is not equal to 'Descoped'.
I have tried:
Set rng2 = Sheets("Execution Status").Range("B2:F420").Value "Descoped" and nothing gets selected.
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.
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue If AI3=D3 & D3 is red, format AI3 blue Otherwise, leave AI3 unformatted.
I have an employees database contains IDs expiry dates and other data .. what I want is the following:
1 - Check expiry IDs dates for each employee's.
2- If there is any ID that is already expired or will expired after 15 days from now, its data should be listed in the required sheet.
3- The information that should be listed is the following:- A- Employee Name. B- Employee Badge Number. C- The expired ID date under its ID type as per the attached workbook.
4- If the ID is not expired as per the mentioned above conditions, its place in the Expired ID List should be left blank.
I actually tried the following code, but it doesn't provide the required aim.
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
I have a row of different dates in a speadsheet all in the same column e.g A1 to A10 What I am trying to achieve is that when it reaches todays date it will automatically change that cell to read Expired.
Basically I have a list of employees. This list is simple in that it shows their off time "From:", "Till:". in columns to the left of there name. So in Cell A:A would list all of there names.. In the example sheet I have just a few listed. To the left of their name if "From:" (column B) and then "Till:" (Column C). And this From, Till repeats itself for several columns so that multiple off-times may be made for each employee.
Basically what I want to do is delete any sets of dates that are expired (based on the "Till:" date) and shift the remaining dates over. So in other words, if Sam has a date in there of 8/1/09 to 8/1/09 meaning he is going to be off for just that day, and today's date is 8/2/09, then that "from" and "till" date of 8/1/09 to 8/1/09 would get deleted from his list and the remaining dates (if any) would get shifted to the left.
Hopefully I have provided a good enough explanation of what I am trying to do here.. Please let me know if you have any questions! I have provided an example the reflects my setup (and the setup can be changed if it makes anything easier)
i would like to display all the employee whose passport and visa is almost expired (30 days before expiry) to another sheet. I have done conditional formatting highlighting both fields 30 days before expiry but i want to display the list on another sheet.
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
I have a hard time when it comes to "If then" statements and conditional formatting. I have some data that I am collecting and entering into a spreadsheet for a couple different companies. Once I receive paperwork from those companies for the data collected I mark the data with a blue colored text. I would like to have a second sheet that generates what companies have not sent me their paperwork. "If the data is black text, then generate <name of company> on second sheet." The list is small right now, but as the project continues it will grow very large and it will become difficult to track. I will attach a copy of the spreadsheet for you to see the situation. I would like to learn how to do these types of formulas, is there a tutorial geared towards these types of situations?
Must admit the R1C1 still confuses me, but somehow I have this working. Currently the RC4 displays in conditional format as $D8 and the RC4 as $C8, but I'm confused at how it knows to start at R8? Is there any way to make the formulas read ="=$D8=""No""" instead?
I have two spreadsheet "book1" with data in it, "book2" that takes all the information from "book1" the cells are linked in "book2" from "book1" so when someone updates "book1" the information is viewable in "book2"
In "book2" where I am getting stuck is as follows,
Column A contains data "break time" that is formatted in HH:MM:SS column B contains a "total duration" in HH:MM:SS. what I need to do is if column A data is = to or more than 4% of column B then I want to turn the cell in column A "red" if it is less then turn it "green". I have tried conditional formatting and it appears to work the once only, I dont no if this is because its 2003 and not 2007??
What I need is to repeat the conditional format over and over again forever is there any code for this I can use?
I have used conditional formatting various ways to format an individual cell. I was wondering does anyone know if it is possible to conditionally format a row? Eaxmple: If D7=Manager, then format A7 through Z7. If so, how would I go about setting that up?
I'm wanting to create a formula in column B that will return a value of 10 if column A is any value between 70% -100%, will return a value of 5 if column A is any value between 50% - 69%, and will return a value of 1 if column A is any value between 1% - 49%. Can you help me?
I want to do an if (I think I do??) command so that if a1=b1, then the cell turns blue and if a1=0, the cell turns red, with the false statement just being blank.
I have my formulas in place and they are working. The reason why some are coming up with #DIV/0 is because I do not have the data to complete the calculation.
Is there a way to make the #DIV/0 error go away until I imput the data to complete the calculation? I was thinking there might be a way to do it with conditional formatting.
Sub Macro1() With Range("A1") .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:="=A1=B1" .FormatConditions(1).Interior.ColorIndex = 3 .FormatConditions.Add Type:=xlExpression, Formula1:="=A1B1" .FormatConditions(2).Interior.ColorIndex = 50 .AutoFill Destination:=Range("A1:A29"), Type:=xlFillDefault End With End Sub
If my active cell is NOT A1, the formula entered into conditional formatting is off. The range references get all messed up. For example, if my active cell is Say D5
Condition 1 is =IT65533=IU65533 Condition 2 is =IT65533IU65533
But if I run the code with A1 as my active cell, it gets entered correctly as Condition 1 =A1=B1 Condition 2 =A1B1
I would like to add conditional formatting for the whole row based on whether the value of cells in column A is "CON".
For Each c In r If c.Value = "CON" Then c.EntireRow.FormatConditions.Delete c.EntireRow.FormatConditions.Add Type:=xlExpression, Formula1:=c.Address = "OK" c.EntireRow.FormatConditions(1).Interior.ColorIndex = 35 end if next c the "CON" is a formula and can later take the value "OK".
The conditional formatting should highlight the whole row as green when that happens.
