I have some VBA code that formats my sheets and works fine, but the issue is that I run this same piece of code more than once in different sheets with the only difference being the formula references a different cell.
' Green Cells indicate that the supervision is within the year
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=Datedif(D8,today(),""m"")
If the value in column AE falls below 100 it needs to turn yellow. If the value drops below 50 it needs to turn red.
But if the figure in column AB is 0 then i dont want it to do anything at all.
I want to set a conditional format to cell A1 with a value in cell P1 When the value in P1 is between 0 and 10 the conditional value in P1 should be set.
Note
cell A1 is also set with a conditional format to put a border around it when there's a value in A1 (cellvalue is not equal to " ")
So the formula referencing P1 would be a second condition.
I have this Macro for Excel which imports 1 file C:datafilesuser1-data-1.txt into cell E52:
View 6 Replies View RelatedI'm trying to reverse engineer a spreadsheet (see attached) and apply the data table to my own spreadsheet. The inspiration spreadsheet (attached) utilizes a 2 variable data table and conditional formatting, where the cells are formatted based on their value in relation to a variable called "Gross". So the rules look something like this:
Cell Value Between 0 and Gross-1
Cell Value > Gross
Cell Value = Gross
Cell Value < 0
Graded Color Scale Minimum ="<385000" Maximum =">385000"
I assume the Gross is calculated as 25% * 350,000 = 87,500. The hard coded value of 385000 is actually incorrect, because it's based on a percentage of 10%... but I'll let that slide The resulting formatting of the data table would support the hypothesis that "Gross" = 87,500. However, when I reset the Conditional Formatting to reference another cell with the calculation result of 87,500 it completely throws off the formatting. I'm simply stumped as to how they are using the text "Gross" as a variable. How/where are such variables set so they can be used in conditional formatting?
How to apply the data table logic itself to another spreadsheet.
I can't figure out how to set up conditional formatting to let me know if the value of a text cell is NOT X or Y. In other words, the only appropriate responses are X or Y (or Y or N or whatever the case may be) and if it's NOT one of those two letters, I want to apply conditional
formatting. Also, will it accept either upper or lower case (I'm fine with it either way).
I have an issue regarding use of conditional formatting where the range i wish to format is on variable length.
For example; lets say i want to format F15 downwards depending on there being a value in the cell, how could I do that ?
Lets say I want the cell to turn GREY if number is 0, RED if the number is below 85, GREEN if above 95 and AMBER otherwise.
How could I do that ?
Any how could i ensure it picks all rows in the range where numbers have been added
I have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values. I have the following
Sub CellCCondFormatting()
Dim j As Long
Range("C2").Select
j = Range("C2"). CurrentRegion.Rows.Count
MsgBox ("1st: " & j)
Range("C2:C" & j).FormatConditions.Delete
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4
MsgBox ("2nd pass: " & j)
End Sub
It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on....................
The spreadsheet I've been working on is functionally working how I would like, but I am looking to improve it cosmetically. I have a range of cells in which I input scores. Those scores are averaged in blocks of the value of cell B1. I would like to fill the cells yellow if they are a multiple of the value of B1.
View 2 Replies View RelatedI am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.
View 2 Replies View RelatedI have a spread sheet that shows dates when people have been assessed to be fit to carry out exercise I want to be able to put dates into a cell that are older than today e.g. 07.07.2013 and it turns the cell green then 11 months from that date the cell turns yellow and 12 months from date cell turns red. I can get conditional formatting to do this from the present date but not from older dates .
07,. 07. 2013 = Green
07.06. 2014 =Yellow
07.07.2014 = Red
I'm trying to clear conditional formatting (icon set) that I was experimenting with, but no joy at all. The only function within the "Styles" section is Cell Styles -- conditional formatting & fornat as table choices are grayed-out.
I've tried to delete and re-enter the data, clear formats, clear all, copy and paste, format painter...nothing worked.
Using Excel 2010
I need to "Fill" cells A1 to A10 with a fill colour if cell A1 contains anything (Value or characters)
Is it even possible using Conditional Formatting rather than VBA?
What I'm looking to do in Excel 2013 is have it so a specified cell loads an image based on the results of a range of cells.
For example:
If cell B1 = red
and cell B2 = up
and cell B3 = yes
then cell A5 shows image X.
But then have it be comprehensive enough to do
If cell B1 = green
and cell B2 = down
and cell B3 = no
then cell A5 shows image Y.
My hangups are trying to get different images to load in the same cell. Also, all the examples of this I've found are pulling from one cell where I need it to display an image based on the results of multiple cells.
I have a row of data in Excel 2013 that is roughly as follows :
Cell A2 - Date || B2 - Status || C2 - Description || D2 - Estimate Time || E2 - Comments
Cell B2 is a pick list of statuses --> Open || In Progress || Closed
The conditional rule I want to create is one where if B2 = 'Closed', format the text of the entire row (cell A2 to E2) to turn light blue.
I was successful in doing this --> basically highlighted A2 to E2; conditional formatting; new rule; use formula; $B$2 = "Closed"; format text color blue.
When I do this, now when cell B2 = Closed, the line turns blue.
The issue is I want this condition to apply to row 3, 4, 5, 6, 7 etc ... basically a multi-row list.
I highlight the cells (A2 ~ E2) and then grab the bottom right corner of E2 and drag down 5 rows (creating row for A3 / A4 / A5 / A6 / A7).
The issue is when I set B3 to Closed, nothing happens; likewise for B4 or B5 etc ...
When I set B2 to Closed, it changes itself and all the rows below to light blue.
This used to work in earlier versions of Excel, but I can not see to get this working in 2013.
I'm running Windows XP with Excel 2010. I would like to be able to have a group of 19 boxes of which each box has a unique entry, ie 1, 3B, 5C etc. Each box I'd like to have a way in which when selected only the single or dual unique characters show as a color. See attached spreadsheet with sample. When a cell with lets say 1 is selected, a drop box appears and the color and description are shown. Select a color but only show the 1 and the color selected, leaving behind the description. How can I do this for all 19 boxes. This seems like its a classic conditional format. Unfortunately it appears to be several orders of difficulty than a normal conditional format.
View 2 Replies View RelatedI need to generate conditional formatting in Excel 2010.
For the planned date of material submittable should turn yellow before 10 days ago & planned date of material submittable should turn Red when overdue.
I'm trying to do conditional formatting that applies to an entire column (that way there are fewer conditional rules), but it only applies to a certain range of cells within that column. So what I did was setup the rule such as:
Applies To:
=$B:$Z
Formula I want to use:
=AND(MOD(ROW()-3,49)=38,INDIRECT("B"&ROW()-4)>=0)
If I copy the entire formula into a cell on the spreadsheet itself, it works just fine returning TRUE and FALSE depending on what row I'm on.
However, the above formatting rule does NOT format ANY row. But if I remove the AND function and the first condition (the one that limits which rows it's actually going to apply to) then it will work...OR if I do NOT use the INDIRECT function then it seems to work, it's some weird combonation of using the two together that is causing it to not evaluate to true.
Oh, and I thought of trying to get the formatting to work with the INDIRECT...then I put in the AND function but the only parameter was the original INDIRECT function (see below)...it stops formatting!
=AND(INDIRECT("B"&ROW()-4)>=0)
Office 2010 on Windows 7 64bit
I'm working in Excel 2003.
I'm looking for a way to use conditional formatting to flag a cell in a list if the user enters a value that already exists in that list. Just trying to keep any values from being entered twice.
I understand how to set cond form to flag a cell, but not what criteria to enter to activate the formatting when a value has already been used.
Some sort of =IF(COUNT...?) function?
Conditional formatting format: Boarder - bottom boarder
The problem when I insert a raw in the middle of the table the condition formatting is not applied. How can I make excel carry over the conditional formatting?
The only reason I formatted the data in a table because it'll automatically control alternating row background color when insert rows.
I'm currently trying to set up a spreadsheet on excel, when certain documents need updating. Basically I have a column with the dates in that each document was last updated, I want the cells to turn red (fill colour) if the document has not been updated within the last 6 months and orange (fill colour) if the document has not been updated in the last 4 months. How do I do this?
I am currently using Excel 2002 and am on a Windows XP computer.
Using Excel 2013.
I have two columns of numbers. For a row, if the right number is greater than the left number, then color it green, if it is less then color it red.
e.g.
A1 = 100 B1 = 101, then B1 gets colored green
A2 = 100 B2 = 99, then B2 gets colored red
etc.
I have clicked on conditional formatting with A2 selected, then chose A1 for values greater than and chose green. I then copied the formula for all rows. I then did the same for values less than and chose red.
It is not coloring the cells correctly. On some rows when B
I have data in several columns going left to right. In column N I subtract column H from column I and then in column O I subtract column I from column J. I then use conditional formatting to indicate whether column N is greater than 45 and column O is less than 120, using different formats for each. If the cell value does not meet the condition then there is no conditional formatting used in that cell.
I want to subtotal each column to indicate the amount over or under 45 in column N and over and under 120 in column O and I would like to know if I can do it using the colors in each cell. Most rows do not meet either condition but a few rows do meet both conditions and I don't think sorting and subtotaling is not going to give me what I want.
I have about 7,000 rows in this worksheet and I am going to be removing lines and then maybe adding them back and I would like to track the balances as the data gets moved around. I can use Window Watch for that.
I am using Excel 2007, don't know how to write VBA and would really like a solution that does not use VBA.
I made a planner for shared use.
Is there any way users can cut and paste while keeping the Conditional Formatting of the source cells intact? It is the same as copy-paste and then delete the source cell contents after copying.
Excel 2003
I have a spreadsheet where we track our contractor's Worker's Comp and General Liability insurance certificate expiration dates. I want the expiration date to highlight in red if it is expired and to highlight in orange if it will expire within 30 days or less.
I have attached a testing sheet similar to what I'm working on. The F column has the dates that need to highlight. I have MS Excel 2010.
I am trying to do a conditional formatting based on the result in one cell but it needs to add up multiple cells first to determine which conditional format it needs to use. I am not sure whether I need to do a formula conditional format or whether to use cell is greater than with =sum() in it. I have tried both but neither seem to be working correctly. I have attached an example of the spreadsheet and the conditions I am trying. I am using 2010 version.
Basically, the closing amount for Monday in prod 1 (cell c4), needs to display in red if sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4) & Fri (R4) is greater than that value or display in amber if the sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4).
I need to complete the same for the whole column for Monday (col C) for each appropriate product. Then the same for column B but this will sum different cells but the principal will be the same.
As per my attached file : duplicate value.xlsx
How to highlight duplicate value by using Conditional Formatting in office 2010.
Actually i Know how to use this feature in sheet "example" of my attachment.
But i want to highlight inv# 457878268 in cell E9 of sheet "My requirement" if it is exist above in cell E2 to E6.
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..
Using Excel 2007 and conditional formatting, how would I find duplicates across all fields in the table, similar to the "remove duplicates" tool that in default mode selects all the fields or offers the option to select fields of choice. I need to look at all the fields.
I've tried concatenation, but only with a limited number of fields; larger amounts slow the process to a crawl. If my table is 10,000 records with 15 fields, what process would I use to highlight the duplicates. How about something in VBA?
I have a date in column A. I want to format to YELLOW column F based on whether column F is blank and 20 days past column A. I also want the formatting to change colors to RED if it remains blank past 30 days. Then, when column F is filled in I want the formatting to be removed.
I am working in Excel 2010