how to display the current set of conditional formatting? I don't want to go to the cells I want a listing of all the conditional formatting rules. I am sure I have seen this somewhere in the past but cannot locate it.
View 3 RepliesI have two conditional formatting rules that work independently, but I am looking to combine them into 1 rule, making (2) subject to (1) when (1) is true
(1) =$B3:$B6="y"
(2) =AND(COUNTIF(K$3:K$6,MIN(K$3:K$6))=1,K3=MIN(K$3:K$6))
I have a spreadsheet with many rows of data. I want to be able to see the last month or so of data (each day is one row), but I do not want to have to scroll to the bottom of the worksheet to see the recent data, so I want to reverse the data - I want Row 1 to be my column headers, and for every new day I want to insert a new row at Row 2 and add my data. However, when I do that, my conditional formatting gets all messed up. Once the new row is added, the formatting rule changes its range to only those rows which had data in them before I did the insert, each new row gets a separate but identical rule that applies only to itself, and the headers row gets its own rule.
That seems incredibly stupid to me. Is there a way I prevent the formatting rules from changing and just stay the way they are which applies to the entire column?
How do I create 3 color conditional formatting formulas for the following rules?:
if today is 15 days or greater before the day in the cell then green
if today is between 14 and 7 days before the day in the cell then yellow
if today is within 6 days to zero days before the day in the cell then results
anything is clear
x8:x21
5/10/2012
5/14/2012
5/21/2012
6/11/2012
6/12/2012
6/12/2012
6/12/2012
6/20/2012
6/22/2012
6/23/2012
7/4/2012
7/5/2012
7/8/2012
7/12/2012
I'm having to successfully add three conditional formatting rules, and associated cell interior colour formats if any of the rules are met.
Each time I run my macro, I want to refresh every cell, within a defined range within a Col P, with these rules.
So far, I can add the three rules to each cell, but when I try and add the format colour - for when a rule is met - I keep getting a 'Subscript out of range error'.
(As an aside, is my For / Next loop approach the best one to take to add the formatting to each cell, or can it be written much simpler?)
Here's my script:
VB:
Dim Col_P As Range
Dim Cell_in_ColP As Long
'Set up range of cells to add conditional formatting rules to
Set Col_P = Worksheets("tRIIO Pack Notices").Cells(2, 16).Resize(Lrow - 1)
[Code] ......
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
Whilst it is possible to display all the formulas on an Excel 2003 worksheet, it is only possible to display conditional formatting cell-by-cell. This is tedious when checking for any errors, which are easy to acquire when pasting from one cell to another.
View 14 Replies View RelatedI am working with Excel 2000
If cell D4 has a 90 or above, I want cell C4 to highlight in green, but I also want the cell to display the word green. I know how to do the former, but the latter perplexes me a bit.
I have a worksheet with a lot of different conditions applied to a lot of cells. I'd like to do some housekeeping, but to do that, I would like a clear and complete list of all cells that have conditional formats attached to them, and what the formula/criteria is for applying said format. I don't even care what the formatting is, but that would be nice too.
So, when I go to my "Conditional Formatting" screen, I get something like:
Now, this doesn't show me much of anything about what the formula actually is. I need to select it and look around. I'd rather just get a full listing of what the rule says...
And where I might have the same rule applied to different ranges of cells And so on.
I have a range of cells with numeric values or blanks. I set up a Conditional Formatting rule with this range selected: New Rule > Format only cells that contain > Format only cells with: Blanks; then I set the formatting to a light green fill and on the Number tab > Category = Custom > Type: "blank"
The blank fields get the light green fill, but no text; that is, the value displayed is still blank.
I go back to edit the CF rule and change Blanks to No Blanks. The results are what I expect: the cells with numbers display the text blank and have a green fill; the blank cells have no fill (white).
This is my testing criteria. I eventually want the empty cells to display 0 (zero). I tried setting Custom > Type: 0 (the number placeholder zero) and Type: "0" (literally the number zero), and neither works.
I have tried this with Format only cells with Cell Value equal to [the address of a blank cell].
I tried to set the value as "" but kept getting =""""; and ="" became ="=""" ???
I have tried this with Use a Formula... using the len()=0, isblank(), and other approaches, all with the same results.
I also made sure the option [x] Show a zero in cells that have a zero value is checked.
With all approaches to identifying the blank cells, they ARE obviously being recognized as blank values: the fill color is being applied to the right cells; and when Not Blank is the criterion (or a negation of a formula), the blank ones are not formatted. But blank cells will not display the text as defined in the Custom Formatting.
And this isn't an issue of "If the cell displays 'blank' it's no longer blank, so the rule doesn't apply" - HERE'S WHY: I set up a second CF rule that sets the Font to red when the value is greater than zero and had this CF rule follow the CF rule for No Blanks (the test above that works). Cells with positive numbers displayed blank in red, cells with zeros or negative numbers displayed blank in black. This clarified that the actual value of the cell is being evaluated, not the displayed value after Custom formatting is applied.
I need to add more conditional formatting to my spreadsheet. But it seems to belimiting me to 3. Is there a way of increasing it?
I have a long set of data that I extract a "table" out of based on index matching controlled by 4 different criteria. This part is working as intended. However I need to format the cells with the corresponding "fetched data" based on another criteria. Makes sense? Have a look at a spreadsheet i made representing my problem. It has comments for the problems.
excel tableformatting problem.xlsx
Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.
View 9 Replies View RelatedI have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
This also includes the WHITE sheet.
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 RelatedMy data currently looks like this: 2iiaRXl.jpg
I would like to render it into something like this: TZ1ibNb.jpg
I tried to use pivot tables but I couldn't get it to look right: Jyn1CKl.jpg. If possible, I want the companys' ratings to be displayed along each corresponding year row, similar to the picture in the second link.
Is it possible to format the message box display? I need to display currency format, how can this be done?
View 3 Replies View RelatedWithout using a copy/paste macro, is there a way to display the text and its formatting from another cell? Obviously, the usual formula "= A2" only brings the text from that cell. Example:
A3: un-am-big-u-ous
G4: = A3 (but I want the font formatting used here as well)
I have a spreadsheet that we use to generate visual data, with very small column widths. I have a column width of 1 at the moment, and some data displays, while some doesn't. I want it to show a number which appears every 5th column, without having to merge and center each set. I used to use a macro to merge and center across the data set but the data set length is now dynamic so that is difficult.
View 1 Replies View RelatedMy question is a display/ chart format question. I need to convey what I am showing on this chart in a less "busy" fashion. Is there a way to display the data in an easier to read and see format? In particular, is having both averages on the chart in the same format an issue?
View 2 Replies View RelatedConditional Calculations & Display Of Cells. (See attachment)
View 2 Replies View RelatedCan you make the first part (the logical test) be anything other than a numerical value? I want a value to display only if a check mark (using the wingding font for the check mark) appears next to certain items in a list. For example: =If(A1=checkmark,B1+C1,""). If I am not clear maybe I can explain it another way.
View 4 Replies View RelatedI can't figure out how to use the custom number formatting to get 20130522A to display as 2013 05 22A. I don't care if it has to be 2013 05 22 A, the extra space is fine.
I have been googling and trying different things and I cant get anything to work. All of the characters can be text, there will always be 4 characters, a space, 2 characters, a space, and the remaining 3 characters. It seems as though it should be so simple to add two spaces into a fixed length string, but I guess not.
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 -