# Conditional Formatting To Highlight Cells With Formulas (versus Constants)

Nov 24, 2010

Is it possible to use conditional formatting to highlight cells that use formulas, as opposed to having constants?

I have a sheet that uses formulas to provide a default value, but you can type in a number if you want to replace the default. I want to be able to easily identify which ones use the default formula.

If I use functions like FIND, they look at the result of a formula, and not the formula itself.

I know I can write a UDF that will figure it out but I was wondering if there is some built-in way.

## Conditional Formatting To Highlight Cells With Formulas

Dec 29, 2008

I have searched for a while but can't seem to find where the original thread is at. Could someone point me in the right direction?

## Highlight Cells Using Conditional Formatting

Apr 5, 2013

conditional highlighting.PNG

Is there a way I can have excel automatically highlight all cells within A1:A3, B1:B3, C1:C3 if and only if they are greater than the percentage in E5? If so how would I go about doing this?

## Conditional Formatting : Highlight THE CELLS

May 16, 2008

I need to hightlight from A1:A18

In the above cells I have values from 1 to 4

So I need to fill the cell with 4 different colours.

If cell equals 1 = Green
If cell equals 2 = Yellow
If cell equals 3 = Red
If cell equals 4 = Blue

What I do is highlight cell from A1:A18, clik on 'Format', 'Conditional Formatting', I've tried using 'Cells Value is' and 'Formula is'.

## Conditional Formatting (highlight The Cells Red )

Dec 9, 2008

i am trying to highlight the cells red in the b column if a3=d3 (if names match) and if b3<e3 (only if name is "a" should e3 be used to compare with b3). hope this makes sense. i've attached a sample worksheet.

i've tried =if(b3<e3, (vlookup(a3,d3:e33,1,false),true)...but does not work correctly. i need the average weight to remain constant according to the name when comparing, but if i drag the formula down to other cells, the average weight does not correspond to the name.

## Conditional Format - Highlight Cells In Column That Have Entered Numbers Rather Than Formulas

May 29, 2013

Col A - budget items (description)
Col B - budget dollars
Col C - actual dollars
Row 10 - summation Cols B and C

When the budget is prepared the actual dollars in Col C are equal to the budget dollars for all items. For instance the formula =b2 is placed in cell C2. As time passes the user records actual dollars in Col C for each item by entering the actual amount directly into the cell. Actual dollars do not become known all at the same time, so that Col C will contain a mixture of formulas and entered numbers.

How can I highlight the cells in Col C that have entered numbers rather than formulas.

## Flag Cells With Constants In Formulas

Feb 6, 2008

I'm dealing with a lot of spreadsheets put together by people who are not that familiar with Excel. Because of this, there are many instances where the formulas created include constants. For example: = A1 * B1 + 200. I want to create a VBA routine that will cycle through the cells I selected and "flag" those cells that have a constant in the formula.

## Using Conditional Formatting To Highlight All Cells - Getting Error?

Aug 28, 2012

I am using conditional formatting to highlight all cells where the value <> 0.00. It's working for 98.9% of the records, but for some reason, about 5 of the records with 0.00 are being highlighted for some reason. I've checked the field formats and they're the same as the other numbers.

## Highlight Cells If Data Differs By Using Conditional Formatting

Feb 6, 2014

Refer to attached sheet. I have 2 sets of data to compare.

B4:H30 and K4:Q30

Compare B4 with K4,C4 with L4 and so on.

If any data is not equal then highlight.

Compare Data.xlsx‎

## Conditional Formatting A Column And Highlight The Cells That Fall Within The Top 20%

Sep 16, 2009

I have a spreadsheet showing names, quality percentages and times taken to answer a call in 3 columns. I would like to highlight the cells that fall within the top 20% of those shown (e.g. if there are 100 quality percentages I want to highlight the top 20 not those over 80%) also the same with call length.

I have a column of numbers and times (mins and secs) and what would like to do is set a conditional format so that the cell turns a colour when the number of any cell is within the highest 20% and the other column turns a different colour when the time is within the lowest 20%.

## Conditional Formatting :: A1 Is Equal To Data Highlight Cells On A2

Dec 15, 2009

how to create a conditional formatting formula that looks like this..

"If A1 is equal to DATA highlights greater than zero on cells A2 to A1000.

## Conditional Formatting Cells That Contain Formulas - NO VBA?

Mar 28, 2013

I need to apply conditional formatting to cells in a column that contain formulas, or more accurately cells that do not. I have a spreadsheet with default values in a column based on a lookup function. I need to know if the user overwrites the default and cannot use UDF's or VBA. I don't think it's possible so my alternative is to include a hidden column that does the same lookup and do the conditional format off of a comparison.

## Using Conditional Formatting To Highlight Cells Based On Approaching Dates

May 25, 2013

I have a sheet where i need to highlight cells C3:C140 based on approaching dates. The cells C3:C140 contain dates generated from another file and would be random approaching dates.

The conditions are

1) Approaching Weekends (Sat and Sun) = always turn red
2) 3 days after today = orange
3) 2 days after today = dark orange
4) Other days = light orange

## Format Formulas, Constants, Numbers & Text Cells Differently

May 8, 2008

I am attempting to write a VBA function that will add textual formatting to each cell that has content. I am just learning VBA; so far, as an example I have

Sub NewSub()
i = 1
j = 1
Do
Cells(i, j) = "'" & Range(i.j).Value & "',"
i = i + 1
Loop Until i = 40
End Sub

but, as I'm sure you'll notice, it does not work. In essence I want to wrap quotes and follow with a comma each cell that contains any content. The actual content test I haven't gotten to yet.

## Apply Conditional Formatting To Range Of Cells That Contains Formulas?

Aug 11, 2012

Is there a way to apply conditional formatting to a range of cells that contains formulas?

## Copying Conditional Formatting Formulas To Multiple Cells?

Jul 9, 2013

I have applied conditional formatting to a cell using formulas and i want to copy that formatting to a new cell using similar formula but pointing to different cells. Is it possible to do so? I have tried to copy and paste special>formats, but that just copies the conditional formatting with the same formulas.

Sample: =IF(\$C\$5/\$C\$295>\$R\$5,TRUE,FALSE)

I want to copy to \$D\$5/\$D\$295>\$R\$5,TRUE,FALSE) without having to paste the new formula in each time.

## Test X Conditions In Conditional Formatting: Highlight Cells Is Two Conditions Are Met

Oct 17, 2007

I would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND(\$J2=0,\$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard

## Conditional Formatting To Highlight A Value

Jun 2, 2014

Please refer to the attached data sheet. I need the D27 to be formatted so that it shows a value only if the value in D8 exceeds the value in D7..

## Conditional Formatting Highlight

Dec 25, 2009

I have a list of percent item. I want to use conditional formatting to highlight items greater than 50%. I do as following:

- Hightlight items, C2:C5, then choose Format ->Conditional Formatting
- I choose Formula is, and the format in the condition box is =">50%"
- Finally, I choose Format to choose the color.

I think I do the right steps but Excel doesn't show anything.

## Detecting Formulas Vs. Constants

Oct 1, 2006

In my company we have many spreadsheets with sections that contain formulas, which may be overwritten with "hard coding", manually entered constants, in certain situations.

Is there a way I can detect the differences between the two types of cells? I would like to setup conditional formatting that would highlight constants in a table that would generally only contains formulas.

## Conditional Formatting Highlight In Different Colours

Nov 3, 2008

In a range, say A1:D5, I need to format cells as follows:

1) If cell contains only "H" or "HV": highlight red
2) If cell contains only a number: highlight blue
3) If cell contains a number with the letter "F" ("1 F" or "5.5 F"): highlight pink

## Conditional Formatting (highlight A Cell)

Feb 22, 2010

I am working on a spreadsheet in Excel 2007 and i am trying to use a formula in the conditional formatting that will highlight a cell if the date is prior to today's date and the next column over is no. the formula i am using right now is =IF(F2>TODAY(),IF(G2="NO",TRUE,)). the problem i am having is that it is highlighting all cells that have a date and the next column over is no.

i can't figure out how to get it to do an if this, then that function correctly. one other thing that could potentially be causing my problem is that the cells.
I am trying to have highlight are calculated themselves based on a formula. see the attached spreadsheet for information.

## VBA Conditional Formatting To Highlight Certain Conditions

Oct 2, 2009

I have a spreadrsheet (sample attached) which populates a calender due to columns on seperate sheets being filled with text and corresponding dates. I started using conditional formatting to highlight certain conditions but soon realised I need more than 3 so I tried looking into VBA - and failed.

All the cells in the calender have the 3 usual conditions associated to them but in addition I need to be able to colour individual cells if they contain certain phrases or parts of phrases like "BP" or "Current"

## Conditional Formatting Highlight The Duplicates

Oct 4, 2008

I have a worksheet that has times based on 7 minute intervals. If I post 10:37 AM in cell b5 then again in cell b6 I would like b6 to highlight.

I'm trying to use countif(\$b\$3:\$b\$15,b2)>1 to highlight duplicate times in column B. For some reason it will not work for me. Should I use something different?

## 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.

## Highlight Holidays Through Conditional Formatting

Jan 25, 2012

I would like to highlight the holidays (for Belgium) through conditional formatting if possible or something else.

Exemple:
If D1 (= an holiday)
than highlight in grey D1:D34

How to proceed?

## Set Up Conditional Formatting To Highlight Smallest Value By Row?

May 30, 2014

how can I set up conditional formatting to highlight the smallest value, by row?

## Conditional Formatting (>) - Highlight Rows

Jan 12, 2010

I have data from Col A to Col M, in Col C i have amounts, i need a conditional format that will highlight rows from Col A to M in yellow which are >5,000,000.00 and

## 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

## Highlight Row Range - Conditional Formatting

Apr 28, 2007

I have a spreadsheet of flight schedules (it has person's name and other details). How do you highlight the row within the table when a cell in the row has today's date and another condition when date is today+1