Using Text Variable For Conditional Formatting
Mar 11, 2014
I'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.
View 3 Replies
ADVERTISEMENT
Mar 1, 2010
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).
View 9 Replies
View Related
Apr 30, 2009
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
View 7 Replies
View Related
Jun 16, 2008
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....................
View 2 Replies
View Related
May 23, 2013
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 Related
Jul 9, 2012
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"")
View 4 Replies
View Related
Jun 12, 2008
I 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 Related
Oct 26, 2011
Basically, I have a Rule that says:
=$A1="Mitigated"
I want the same format if Column A says either "Mitigated" or "Accepted".
I can't create a new rule as I have to save the file in 2003 for other users and I've got another 2 rules for varoius other things.
I've tried crudely putting "Mitigated"OR"Accepted" and it dosen't work.
View 2 Replies
View Related
Oct 1, 2007
I am automating an invoice template for an excel novice, thus I want to conditionally format certain cells dependant on what text is in them.
I want to do this in VB but I don't know how!
View 9 Replies
View Related
Sep 28, 2009
I would like to conditional format a range of sells which contains numbers formatted as text. I would like if the sell contains the specific number (which is formatted as text) to be formatted in red. It works for one value but I have to list 15 values for which this condition to be done.
For example: if some of the cells in the worksheet contains one of the following values: 265987,265489,235698,2356987,2356987,2356987,235569,...ets - 15 conditions - to turns red.
View 9 Replies
View Related
Sep 16, 2009
I am trying to set some conditional formatting, but for some reason on of the conditions is not working and I was wondering if anybody know how -
View 3 Replies
View Related
Jun 6, 2013
I 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.
View 7 Replies
View Related
Jul 11, 2013
I have a table that has dates starting from A6 which is a whole month say 01/2/2013 to 28/02/2013 like a gantt template.
I need if the word "Ordered" is in B5 I need X (crosses) in the date cells starting from B6 to end of month until someone changes B5 to Delivered then I need the X to disappear.
test.xlsm
[URL] ....
View 2 Replies
View Related
Nov 14, 2013
A2 contains the word Yes. If A1 is blank, I need to clear the text from A2. Can this be done with conditional formatting? I know I could just change the text to white, but I actually need the text cleared from A2 if A1 is empty. My boss doesn't want me to use VBA so I can't code this.
View 8 Replies
View Related
Jun 26, 2013
I'm designing a spreadsheet for our department which tracks a students progress throughout their time at the school. There are 4 levelsStatementSchool Action PlusSchool ActionMonitorIn year 7, they may come into our school at Monitor level, but when they get to year 8 they may have moved to Action Plus. I want to highlight that change on my spreadsheet with conditional formatting if possible so if they go up, the cell will turn red, if they move down the list the cell will turn green.
I've created two columns on an options sheet (which holds all my data validation lists) and assigned a number to each of the levels so i can conditionally format my columns using < and > but I don't know how to write the lookup formula
What I'm trying to achieve
ExcelHelp.jpg
My Options sheet
Excelhelp2.jpg
View 3 Replies
View Related
Aug 20, 2009
What i want to do is highlight all row instances where that row column10 has the text "Roller" . All i can see is the forumular =$J1="Roller" ive tryed to select just the single cell J and tryed the whole row and a range. Though i get a error message about =-+ quotation ect.
View 5 Replies
View Related
Mar 5, 2013
I have a spreadsheet with employee ID's along with other data, I need to see if any of the ID's are out of this range 0001 thru 1368, now some of the ID's have A,B,C, (e.I 0245A, 1101B,) but some do not have them. if the ID's are out of range I need to highlight it, cant figure this out, because of the Alpha characters.
View 4 Replies
View Related
Oct 29, 2009
I have a conditional formatting rule that checks for misspellings of client names.
My employees input client names into a spreadsheet, and I need for the spreadsheet to notify them via a fill color if they have misspelled the name.
So, I set up a rule for each client name using the specific text criteria.
The rule simply checks for the client's name, and fills the cell green if the client's name is correct. If the client's name is misspelled, then the cell remains unfilled.
This works great if the client's name is spelled correctly, or spelled incorrectly.
But there's one hitch:
If the client's name is spelled correctly, but there is some extra character(s) inputted after the client's name, the cell is still filled green even though the data in the cell is useless because the extra character will cause it to be rejected by the next party down the line in the value chain.
So I'm hoping to find a way to make the specific text containing criteria operate with only the specific text I specify and no extra characters or anything after the specific text.
View 9 Replies
View Related
Jan 14, 2010
I have an IF statement which sometimes returns the word "FALSE" and a SUMIF statement which sometimes returns the error #DIV/0!
I need to be able to hide the text in the cells when either value is returned.
I've managed to solve one of them (#DIV/0!) by using conditional formatting and using the formula =ISERROR(H35) then changing the text colour to white.
I can't think of a soution for the "FALSE"
View 9 Replies
View Related
Jan 20, 2010
I have data in columns A and B,
Column A has dates, column B has text
I am wanting to highlight text in column B in a colour
if date in column A is more than 10 days since date in column A on previous occurrence of text in column B
View 9 Replies
View Related
Aug 7, 2006
I need to employ conditional formatting to text on a spreadsheet - but using more than the 3 colours provided from the Format>Conditional Formatting menu.
I recently purchased the ‘Excel Hacks’ (100 Industrial-Strength Tips & Tools) book by David & Raina Hawley, Hack #92 details how to get around Excel’s 3-criteria limit for conditional formatting using VBA, but this solution only describes multiple formatting on numbers, not text. For example, if each letter of the alphabet were entered into separate cells, I would like each to appear in a different colour automatically.
View 3 Replies
View Related
Aug 8, 2014
Is it possible to insert text an a cell value in conditional formating, i,e Ive got the conditional formatting:
=AND(H$4>=$B5;H$4<=$C5)
I want to insert this text whenever this condition is true once and not to repeay it:
="Load " &TEXT(G$5;"dd-mmmm") -- where G$5 is a vallue cell_
View 1 Replies
View Related
Mar 24, 2014
Conditional formatting. I have been tasked with creating a simple game for a group training project.
The game consists of users filling in the answers to questions (simple one word text answers). My boss wants to have a master sheet with the correct answers, and a subsequent sheet that will then compare the employee's answer to the master sheet. If the employee's answer is correct, then the output sheet simply displays the correct answer, however, if the employee's answer is wrong, he would like the output to display the correct answer and highlight the cell.
Is there a way to have the output sheet highlight a cell only when the answer on the employee sheet is different from the master sheet? I was thinking along the lines of using an if statement to see if the values of the employee sheet differ from the master sheet, but I do not know how I would then use that result to highlight the cells in the output sheet.
View 1 Replies
View Related
Jul 30, 2009
I would like to have cells in a certain column turn green if the word in column J of the corresponding row = Not Changed".
View 4 Replies
View Related
Mar 9, 2009
scenario: Column H needs to be formatted to green if the text in Column F does not equal "Dog". Dog could be either DOG, Dog or dog. I know how to get it done with only dog, but this one is a bit tricky.
View 2 Replies
View Related
Mar 12, 2009
I have a workbook with 4 sheets. Each sheet is set up the same. There are 4 columns. The column headings are Title, Item #, 2009 Retail Price, and Price changed from 2008 (in A, B, C and D respectfully).
I need to find all red text in column C and place a 'Y' in column D. If the text in column C is not red I want a 'N' in column D.
View 7 Replies
View Related
Dec 30, 2012
I want to conditional format based on all of the following cells contain the same text as AT19 or not.
A19 H19 O19 V19 AC19 AJ19
What is my formula?
View 1 Replies
View Related
Jun 11, 2013
How can I get cells in a column to be highlighted if the cell contains anything other than the word "approved"?
View 5 Replies
View Related
Mar 24, 2014
I'm trying to create a conditional format that formats only cells that do not contain one of the names from a range of cells.
For example:
Cell O2 contains Florida
Cell O3 contains Texas
Cell range W1:W2
W1 = Florida
W2 = Arizona
In this example I want Texas to have a strikethrough, but Florida to be unchanged since Texas is not included in the list.
I keep getting an error when I try to do a format of text that does not contain =W1:W2.
View 2 Replies
View Related
Feb 9, 2007
I have two named ranges of cells, validcodes and actions. I want to conditionally format any cell in the actions range, if I enter something that contains certain validcode range values.
Example: ...
View 9 Replies
View Related