Conditional Formatting Of Text Obtained From IF-Statement

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


ADVERTISEMENT

Excel 2011 :: Conditional Formatting Based On Text Match From If Statement?

Oct 2, 2013

I want to highlight a cell if the text displayed from an IF formula is equal to the cell content.

CELLS BK10:BN10 are merged and have entered into them the following text "SELL"

Cells BJ20:BP20 are merged and contain the following formula which currently results in the cell displaying "SELL"

=IF(BM22<=-0.08,"SELL", IF(AND(BM22>-0.08,BM22 < -0.03),"NO INDICATION", IF(BM22>=-0.03,"HOLD")))

Cell BM22 is a percentage calculation of the differences between two different days of volume for this stock and that formula is: =(BH22/BH25)-1 Cell BM22 currently is calculating the result to be -65.65%

When I set up a CLASSIC Conditional Formatting using a formula (="If($BJ$20=""SELL""") to check the if the text in cells BK10 match the text displayed in cell BJ20 - I get no error messages and no formatting?

I am using Mac Office Excel 2011. I feel like I have tried everything including changing the Number selection type of the cells to TEXT. Nothing seems to works.

View 1 Replies View Related

IF Statement Using Formatting Criteria (NOT Conditional Formatting)

May 5, 2009

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.

View 5 Replies View Related

Conditional Formatting Using AND With NOT Statement?

Jul 30, 2014

I wish to use a formula to highlight a date based on two criteria in two separate columns;

The first is the Date that will be in Column B.

I want it to highlight the date if : Today() is greater than that date in the cell plus 5 years (or 1824 days)

The second criteria I want is for the date to be only highlighted if the corresponding cell (in column A) does NOT contain the term ("Basic")

I have the formula:

=AND((TODAY()>(B1+1824)),A1=("Basic"))

which highlights the date correctly IF the term in Column A is "Basic" : But I cannot figure out how to insert the NOT statement to make the formula flip around so it only highlights those dates older than the 5 years requirement and NOT contain the "Basic" in column A.

View 2 Replies View Related

AND Statement In Conditional Formatting

Mar 30, 2009

I have several rows with data in and what I would like would be to total the amount of time "CU" is displayed and if that number is less than say 10 the formatting is applied. I need if possible to be able to do this via the one condition.

View 2 Replies View Related

Conditional Formatting And IF Statement?

Oct 14, 2011

I type this formula and it work only for the first and the last condition, for the other condition it return to me FALSE. i dont now what is wrong,

=IF($C$10=1200,$E$10

View 1 Replies View Related

Conditional Formatting For IF Statement?

Apr 11, 2014

Can you do a IF statement in Conditional Formatting? I need a column to highlight Red if there is a blank in it based on the current time COMPARED to the stated time in another column (on a separate tab)

So if current time is 1:30 PM and the stated (appointment) time on the other sheet is 1:15 PM, and my control field is still blank, I need it to turn Red.

View 1 Replies View Related

Case Statement On Conditional Formatting

Dec 28, 2007

Set MyRange = Range("A1:AZ9615") ' Range to apply format to

For Each Item In MyRange
Select Case Item.Value
Case "1780", "1800", "1810", "2050", "6170"
Item.Font.ColorIndex = 3
'x = 3
Case Else
x = xlNone
End Select
Item.Rows.Interior.ColorIndex = x

Next Item

I want it to search that range and turn those Numbers in Quotes to Red, which it works fine, but Somtimes those numbers are Imbedded in a string excample "1810-1-DAV". So my Question is When It turns my normal numbers red, how can I get it to turn the STRING RED ALSO?

View 9 Replies View Related

Case Statement For Conditional Formatting

Apr 25, 2008

I am trying to eliminate the use of formulas for conditional formatting in my code. I heard that the CASE statement might work for this. Below is the code I have but the case does not like the second case statement.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer

If Not Intersect(Target, Range(("S2:V" & TTRows), ("W2:W" & TTRows))) Is Nothing Then

Select Case Target

Case Is >= 0.8
icolor = 3
Case is >= 0.7 and not >= 0.8
icolor = 6
Case Else
icolor = 0
End Select

Target.Interior.ColorIndex = icolor

End If

End Sub

View 9 Replies View Related

Conditional Formatting With If Statement - Response Due Column

Mar 25, 2014

I created added conditional formatting to the Response Due column, to keep track of when response was due.

I only want the conditional formatting to be applied if the cell next to it is blank.

For example, the last one on my attachment is due 3/26/14 but I have already submitted a response (see column K)

What can I add so that only those dates next to a blank in column K get the conditional formatting?

Sample.jpg

View 2 Replies View Related

Conditional Formatting Using If Statement On Opening Of Workbook

Oct 19, 2005

I have several worksheets in a workbook that has dates of completion of
various training topics. Some of these topics require annual (12 months),
bi-annual (24 month), 6-month, or even 2-month re-training.

What I am trying to accomplish is to automatically shade all cells for dates
that fall within one month of these periodicities based on the computer's
clock when a workbook is opened. Unfortunately different columns have
different periodicities so what I was planning on doing was putting that
monthly number in a cell ("12" in A1000, for example for all Column A
values). When the file is opened, if any of the dates in column A are more
than 11 months from today's date (A1000 minus 1), they are within one month
of needing to be re-trained and I want the cell shading to be a light
yellow.

View 11 Replies View Related

Conditional Formatting With OR Statement And Negative Values

Dec 11, 2013

I am attempting to add conditional formatting (yellow fill) to cells that are greater than 15% or less than -15%. I've tried the following formula but, it highlights all cells.

=or(b2:b5>15%,b2:b5

View 1 Replies View Related

Conditional Formatting Of Text?

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

Conditional Formatting On Certain Text

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

Conditional Formatting Text

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

Conditional Formatting - Formula=text

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

Conditional Formatting Or VBA To Display Text

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

Conditional Formatting Add Text In Different Cell

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

Deleting Text With Conditional Formatting?

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

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 View Related

Conditional Formatting Text To Numbers?

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

Conditional Formatting To Highlight Row On Text

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

Conditional Formatting With Text And Numeric

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

Conditional Formatting Specific Text Containing ONLY

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

Using Conditional Formatting To Hide Text

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

Conditional Formatting Highlight The Text

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

Conditional Formatting, Variable Text

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

Multiple Conditional Formatting On Text

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

Insert Text And A Cell Value In Conditional Formatting?

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

Conditional Formatting Based On Text Of Another Cell

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







Copyrights 2005-15 www.BigResource.com, All rights reserved