i am trying to change the number format of one cell from say "general" to hh:mm:ss depending on the number format of another cell using a macro...
heres what i have so far.
Sub cellFormat()
If Range("F4").NumberFormat = "hh:mm:ss" Then
Range("F2").NumberFormat = "hh:mm:ss"
Else
Range("F2").NumberFormat = "General"
End If
End Sub
but its not working, can anyone see the error of my way?
I have a row of data I want to be blacked out if there isnt a reference number in the first column
So if I type the reference number i can in theory then use the rest of the row but if there is no reference number in the first column i cant see any of the row ...
Can I use conditional formatting to determine if a number in a cell is displayed as a normal decimal or scientific?
So a number of 1000 or above will be changed to scientific notation with 4 significant figures.
The conditional formatting just seems to be about colour/shading/icons
I am trying to do a conditional formatting for the number of working days. What I am trying to do is:
Example:
As in the row D7 I give the actual workdays required to finish the task the Gantt Chart gets a specific formatting on the right appears for the number of working days.
Problem:
I want to avoid the formatting if there are any weekend (Saturday or sunday)
Suppose I give number of working days as 20 then there would be 6 weekend days so it shall add to the overall actual working days but again sinnce we are adding 6 additional days one more weekend day would come in between so the formatting shall take care of this.
I have a list of 2000 pieces of inventory. Only the year of purchase is associated with each one, not a specific month or day. I'm trying to apply conditional formatting so that...
Less than 2 years old = green
Between 2 and 4 years old = yellow
Between 4 and 6 years old = orange
More than 6 years old = red
I've been trying =TODAY()-365 (365 replaced by number of days in the above named ranges) but the cell only changes to the color of the most recently applied formatting rule.
I have in a range B10:B110 formulae that returns a number. When I try and use CF to highlight the lowest number, it does not trigger. Is it not possible to have both a formula and CF in the same cell, that triggers the CF.
The formula to return a number is
=IF(B$5
I would like to change the number format of a cell in function of the value of another cell. For example, if A1=1, then I would like the cell A2 to be displayed as a percentage. If A1=2, then would like the cell A2 to be displayed as a number.
View 4 Replies View RelatedCan I use Conditional formatting (highlights duplicate values) but highlight the number even if the number is an Positive or Negative number.
It must highlight the number if it's -300 or 300 in both instances.
I am trying to apply conditional formatting to highlight the cells in D column if it starts with 91099 or 91004.
I tried to do the same using wild card, and left function but didn't get the desired output.
Conditional Formating Text equaling to Number formats:
Example:
C20 is '436.59 (its pasted as text often is must remain as text) and D20 is 436.9535
I want D20 to have only 2 decimals and turn green if it equals C20. And turn red if it doesnt equal C20.
In a column in which there are repeating numbers is there a formula I can enter into conditional formatting that will highlight a cell when the number doesn't match the value from the value above it?
So in this example the values with an * would be highlighted.
1*
1
1
2*
2
2
2
3*
4*
4
5*
5
I would like to do conditional formatting...
Range (A1:Y192)
Turn the row number (identified in cell AC1) into RED fill.
I have attached a copy of my "budget". What i need is whenever you choose a option in A9 on PayCheck - DEC - 09 - B it will count the number of items on the right and hightlight them with a color. I use =COUNTIF('PayCheck - DEC-09-B'!E$2:E$1000,A9) in A11 to tell me the number of occurences but I would also like a visual effect with colors.
View 5 Replies View RelatedI have a column that calculates & displays the number of minutes between two cells, i would like to conditonal format the column to either display a 2-color scale by number, min 0 and max 40 or i would like to apply a fill to all cells > 40. I can do this with normal numbers typed into the cells but i cannot get it to work with the result of the formula. I have tried formating the cells to number but still i cannot get it to work.
View 3 Replies View RelatedI have a spread sheet where there are multiple rows of price quotes from different suppliers.
Some suppliers have quoted, some however have not and there is a "#N/A" or "no bid".
SupplierA
SupplierB
SupplierC
SupplierD
1.3
No Bid
2.3
No Bid
How can I get a conditional format to check each row for the lowest numeric bid, highlight it but ignore the text?? What I have tried so far doesn't work.
=G31=MAX($G$31:$J$31)
This does not seem to work, I don't know how to exclude the text values from the MAX function.
I have a sheet named "Current" and in Colum A named "VRM" there are registration numbers.
I have another sheet named "Potential Flyers" again Column A is named "VRM"
I would like in Sheet named "Current" to highlight a Reg Number if it is also typed into Sheet named Potential Flyers.
I am trying to figure-out how to set up conditional formatting of dates in a column (e.g. Date Submitted) and have these dates, and/or cell, change color as time progresses. I have Excel 2007.
For instance I have an application submitted on 11/20/2011 in a cell. I would like to have the date change color (Yellow) after 30 days have passed.
Then do the same except change to Red after 60 days have passed the date.
I searched the forums and did not find this particular problem. I of course may have missed it too...
My guess was to try "$A$1
First off I have an excel sheet that I have split into two windows. excel sheet.jpg
I am looking for a formula that will change the bottom sheet number a color if it exists on the top sheet.
when the largest number in column B the hotel in column A should be in bold.
So in excel language IF(Number in B Is Max display corresponding hotel in column A as BOLD. But I can't figure out how to do this.
You can see here on the image:
additionalimage.gif
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
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.
Possible???
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...
View 9 Replies View RelatedI 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.
View 2 Replies View RelatedI wrote an excel program in Excel'03 for a dental office to manage state assistance patients and one of the table columns is the state assistance number. The problem is that the program defaults to a number format when it is entered instead of a text format, which is what I want. The issue is that state assistance numbers are always 8 digits and when it defaults to a number field the program drops the required preceeding zeros.
For example the number 00123456 will sometimes show up as 123456, which is wrong. It's like sometimes it will show up right and other times it gets a butterfly in its brain and deletes those zeros irritating the receptionist here. So, how do I get the table to either stay in text format or set up a number format that keeps the preceeding zeros?
In a column, there are 4 option for each cell -
"PAYMENT DUE"
"PAID"
"UNDERPAID BY..."
'OVERPAID BY..."
These are filled in by a formula
I would like to make them 4 different colours but excel only allows 3 options
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?
View 2 Replies View RelatedI seem to have a problem using conditional formatting with Vba in Excel 2003
When I run -
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?
View 2 Replies View RelatedI 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?
