Free Microsoft Excel 2013 Quick Reference

# Use a formula to determine which cells to format?

If the number in the cell to the left is larger, then make this cell RED.

If the number in the cell to the left is smaller, then make this cell GREEN.

What formula would I use under the conditional formatting tool to make the above two scenarios work? Thanks in advance for your help!

## Related Results

### Conditional Formatting: "Use a Formula to determine which cells to format," ?

Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads
Mortgage
Rent

I would like to have a custom format in a different cell that conditionally will read:

"Rent:" #.00 "Per Sq. Ft."

or

"Mortgage:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

### Can I use a value in a cell, in a formula, to determine what cell is to accessed?

This is a really simple problem, probably with a simple answer if it is possible, but hard to explain.

Say I have a formula (A1 * B1) + (C1 * B1)
This isn't the actual formula I'm using, but It'll do as an example.
Now instead of just always being B1 in the formula I want this cell to be variable. I would like to have a value in D1, say for example 5. Which will then enter the formula so that instead of accessing B1 it will access B5. Then whenever I change the value of D, say to 25, the B5 cell becomes B25 which will be accessed instead.

The way I tried to express this in formula was (B(D1), thinking that D1 would be replaced with its value which with the B would then become a cell address.

Hope that makes sense, hope more so someone can help me.

I probably should have called it 'Can I use a cell that contains a value, in a formula, to access a different cell'

### Using a formula to determine column reference

I'd like to change the column reference in a cell address. I have set up a vlookup list in which the names of the month = a specific letter (ex: January = E).

Can I have a formula in which a cell address (ex: J9) references that letter to determine which column in another worksheet is referenced?
For example, in a formula that reads: ='7765 Act'!R24 can I replace the "R" with with the result shown in the cell where the vlookup is?

The vlookup table looks like this:
April I
August N
December S
February F
January E
July M
June K
March G
May J
November R
October Q
September O

what I want to do is have the table look up the month, for example December, and change the formula to cell S24 instead of the current R24 for November.

Thanks for whatever help you can give, Happy Holidays!
Richard

### Conditional Formatting - Set value to empty cell

Hi,

Is it possible to set a value to an EMPTY cell with conditional formatting?

I am trying this:

On Conditional Formatting:
1) Use a Formula to determine which cells to format
2) =IF(J5=0;"OK";"NotOK") or =IF(ISBLANK(J5);"OK";"Not OK")

Regards,
Elio Fernandes

### Formula to determine under which Header Label a specific value is found

Kindly look at the attached.

I want to use a formula to determine what I have on column C, but with a standard formula I can simply drag down without having to change the reference for each section. The different General Ledgers (GL) are moved within headers regularly and I have to be determining under which header each GL belongs to. Note that no one GL will appear in more than 1 section, and note as well that there may be A general section header and then a subsection which is where the GLs will be listed. I want the subsection header (or the header label that is right before the first number is shown).

Is there a formula you guys can think about. I can't think of any...

### Excel Conditional Formatting Based on another cell

Hi,

I'm trying to make a KPI table which would help me to evaluate my employees but i'm stuck on Conditional Formatting.
From the the attach you can see that i have 4 columns (at the end of column is total):

Evaluation 1
Evaluation 2
Evaluation 3
Evaluation 4

Then total in percentage.

What i want to do is that, whenever on total in percentage is on F11 example up 50.00%, than cell B17 should be colored on red (but number 1 on B17 should stay as it is only the cell color should change, and so on colors can be different)

I searched over about this but i found nothing helpful (except use a formula to determine which cells to format, which still doesn't solve my issue)!

Thank you in advance

KPI.xlsx

### C.F. Date is formatting empty cells, how do I stop that?

Hi, I see many posts close but not exactly what I am trying to fix so hoping someone might see what I am doing wrong.

In Column B is my date column. I created a drop-down that will only offer today's date. Then I added C.F. so that if the date is more than 7 or more days old, turn the cell yellow. If the date is 14 days old or more turn the cell orange and turn red at 21 or more days. I have it set from B3 all the way to B65536.

I choose the "use a formula to determine which cells to format" and I made the =B3

### Using a formula to create named range reference

Is it possible to use a formula to create a reference to a variable
named range? For example, I want a given cell to refer to a named
range. However, the specific named range it would reference is
dependent on today's date. I have named ranges for each weekday, i.e.
Data2, Data3, Data4, Data5, Data6.

In the cell which will reference these ranges, I want to create the
reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any
docs to describe how to accomplish this.

TIA,
Rose

### Need help using a checkbox to determine if a cell gets summed

need help using a checkbox to determine if a cell gets summed. i have
designed a worksheet with items in one column, and how much the item is in
another column. what i want to do is to check a box next to an item and have
the cost of that item added to the other items that are checked. if the item
is unchecked, then resum the items that are checked. i would also like to
add an additional check box that will automatically check or uncheck all of
the other checkboxes. any help will be appreciated.

### Conditional Formatting two statements in formula input

Hello,

I have the following conditional formatting statement that I am having trouble trying to figure out:
```I currently have the working statement

I wanted to add that extra statement of logic but am unsure on how to do that.
This formula was added into the "use a formula to determine which cells to format under "format values where this
formula is true."
thanks,-vargs

```

### Conditional formatting if one cell holds same data as another

On my spreadsheet (example attached) i need to have my numbers (on right) highlighted if a number equals a number drawn (on left).

I've attempted using a number of formula in the conditional formatting dialogue box (using 'use a formula to determine which cells to format'

TIA

Apologies - dinner bell rang

### Using a formula to create named range reference

Is it possible to use a formula to create a reference to a variable
named range? For example, I want a given cell to refer to a named
range. However, the specific named range it would reference is
dependent on today's date. I have named ranges for each weekday, i.e.
Data2, Data3, Data4, Data5, Data6.

In the cell which will reference these ranges, I want to create the
reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any
docs to describe how to accomplish this.

TIA,
Rose

### Can I save conditional formatting rules for use in other workbooks? [Excel 2010]

I frequently use conditional formatting to shade alternate rows.

I use
(1) Home>Conditional Formatting>Use a formula to determine which cells to format;
(2) Type in "=MOD(ROW(),2)=1";
(3) And apply a light gray shade so that it will work on the screen and when I print from my old B&W LaserJet.

Every time I do this I have to search for the correct formula, then go through the steps to create a new rule. I grant that this doesn't take a huge amount of time but it'd be much easier if I could some how save the rule for use in any workbook.

Is it possible to do so?

Thanks for any suggestions.

### Excel 2007 Conditional Formatting accepts formula but does not work

In the attached sample I have tried to find a way to change the background color of a cell depending on the content. But empty cells seem to frustrate my text string conditions. I'm also perplexed by the way Excel 2007 adds it's own quotes to text string entries. I have to constantly re-edit a condition to remove extra quotes.

I'm using the 'Format only cells that contain' rule to apply the CF across a range that defines a given row. That rule lets me place a formula in the cell reference box - but they do not appear to function as they did in 2003.
I also tried the 'Use a formula to determine which cells to format' rule with no better result.

Row 2 has no ConFor, it is supplied as an example of what I'd like to see.
Row 4 shows how Cell Value <> "A" conditionally formats empty cells. The rest of the rows are my efforts to ignore the empty cells.

Row 6 almost works, but row 7 shows how a different 'not between' value set fails.
I want to ignore a designated character, and empty cells.

Am I up against my ignorance - or a bug?
Tried a less detailed post here http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

If I missed an answer to this I am sorry. My searches did not find what I wanted to know.

### Conditional Formatting, using custom formats

Hi Everyone,

I have a spreadsheet I am working on for a prospective retail store. I am currently comparing the potential ROI of Renting vs. Owning.

In the spreadsheet, I have created a drop down list that reads
Mortgage
Rent

I would like to have a custom format in a different cell that conditionally will read:

"Mortgage:" #.00 "Per Sq. Ft."

or

"Rent:" #.00 "Per Sq. Ft."

Can the conditional formatting "Use a Formula to determine which cells to format" make the adjustments (I have already tried several ways to write the formula in conditional formatting with no success).

Any help would be appreciated

### Applying Colors to Top/Bottom Values in a List in Excel 2007

1. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A to select the List.
2. Select Home -> Conditional Formatting (in Styles Group) -> Top/Bottom Rules -> Top N Items.
3. In Format cells that rank in the Top select a number, select the formatting from with dropdown list, and click OK.
4. Repeat the steps above to format the Bottom N Numbers.

1. Select a column in the List.
2. Follow the steps above.
 To color rows where maximum/minimum values are ,in a specific column:
1. Select a cell in the List and press Ctrl+Shift+* or press Ctrl+A.
2. Select Home -> Conditional Formatting (in Styles Group) -> New Rule.
3. Select Use a Formula to determine which cells to format.
4. In Format values where this formula is true insert the formula =MAX(\$F:\$F) =\$F1, click the Format button, select the Fill tab, choose a color and click OK.
5. Add a new rule while changing the formula to calculate the minimum number,
=MIN(\$F:\$F) =\$F1.

Be sure to distinguish between Absolute reference and Relative reference when entering the formulas.

### Conditional Formatting in Excel 2010

Hello all. I am attempting to conditionally format cells in a column of data. I want to shade any cell that is not the same value as the cell immediately above it. For instance, I want the conditional formatting to shade ONLY THE FIRST cell that contains the 460.03 value in the example below. I can't quite figure out how to make this happen. I tried to use Conditional Formatting>Highlight Cells Rules>More Rules>Use a formula to determine which cells to format, and then I just plugged in a < with reference to the cell immediately above, but I couldn't get that to work. I also tried to upload my Excel file, but couldn't get that to work either (probably due to my own ineptitude). Thought I would try without uploading anyway, for now.
541.08 541.08 541.08 460.03 460.03 460.03
All of these cells contain formulas, if it makes any difference.

Thanks

### Conditional Format - Excel 2007

I want to color an entire row green where the value in column C = "Support".
I have selected condtional format > use a formula to determine which cells
to format but am not getting consistent results so something must be
incorrect with the formula.

Thanks

Lisa

### Use a formula to delete part of a text string?

Hi I was just wondering if I could use a formula to delete part of text in a
cell after a certain string of text appears within that cell where the number
of characters before the certain text varies for example:

Big Fat Cat-brown-2803
Small dog-brown-705
Large sheep-brown-106
Small cat-brown-1803

I was looking for a formula so that everything after "-brown" and "-brown"
itself is is deleted. I can only think how to do this where there is a
consistent number of characters before or after the phrase I want to delete,
but unfortunately I don't have that luxury.

Any advice would be greatly appreciated!

### Using a formula to calcualte different mileage rates

Hi,

I have attached a file in which i need some help using a formula to calculate a milage rate once it hits a certain level. So in my sheet the limit for mileage is 10,000 at which these can be claimed at 0.45p and anything thereafter at 0.25p. The opening miles is 9800 which means there are 200 miles left at 0.45p and this is reached on the 4th when 30 miles are done in the day - so i need to calculate 20 @0.45p and10 @0.25p. But i need a formula which will say anything which is under 10,000 charge at 0.45p and anything after that threshold at 0.25p.

I hope this makes sense -this is my first post!

Regards

Andrew.Mileage Rate.xlsx

### Conditional Formatting auto color help

ok, let's see if I can explain this in a non-confusing way. I have a workbook with several tabs and each tab contains essential the same data. Anyway, each column is associated with a month of the year. The workbook is a tracking record of all our employee trainings and what we do is type in the date that we've received the training report. They only have to complete this training every other month and so only every other month is filled.

Here's what I would like it to do.

First, coloring Key: Purple means the report is due that month, green means we've received it.

Say I have four months: April, May, June, July

-If April is filled (with a date) then the cell in June-on the same row-should be colored purple (meaning that June is when the next report is due).

-If May is filled with a date, then the adjacent June cell should be blank (meaning no report is required that month.

-Then, if June has been highlighted as purple because a report is due that month, as soon as a date is entered in, the cell color should change to green.

-And, because I don't want the entire report to be purple until data is entered: If both April and May are blank, then June should also be blank.

-and if possible, if no data is entered after a certain date, the cell color turns red.

I've got the purple to work and be blank if something in the cell before it with the following:
Using a formula to determine which cell to format: =LEFT(F3,1)="" (This is when I'm in cell G3)

How do you have it recognize if something is filled with data without putting a range in, and when there's no data, instead of ""?

Lot's of questions, but only one big one. Any help would be appreciated. I do know that if I did =LEFT(G3,1)="" (again, when in cell G3) it'll turn blank if I type data in G3, but it won't turn blank if there's data in F3.

### HasFormula, Cond. Formatting, and slow spreadsheet

hi all.

I wrote the following function in VBA:

```Function isFormula(check_cell As Range) As Boolean
isFormula = check_cell.HasFormula
End Function```
I then use this formula with Conditional Formatting->Use a formula to determine which cells to format-> isFormula(A1)

It works fine for one cell, but if I copy this format into 30 cells my spreadsheet slows down incredibly so that even just moving from cell to cell has a lag.

Is there a way to make this faster? Using Excel 2007...

Thanks

### Conditional Formatting with AND, ISBLANK, and DATE

Okay, I hate to even post because there are so many conditional formatting questions and answers out there, but everyone has a different way they want to do things, and I can't find a way to make mine work. I'm able to get some things working, but at the same time, it will also be highlighting cells that don't meet the criteria.

Here's what I need.

I have a list columns A-M containing data, C and M are date fields. C will always have a date, and M will only have a date when the row has been "Approved."

If the date in C is greater than 12 days away, I would like the entire row to turn yellow.

If the date in C is less than 12 days away AND the corresponding cell in M is blank, I need the entire row to turn red.

I have managed to get the C cell that is greater than 12 days away to turn yellow by using the "Format only cells that contain" rule with Cell Value - Greater Than - =TODAY()+12

However, it only highlights the cell. I tried using the "Use a formula to determine which cells to format" but couldn't get the syntax right.

I've also messed with the ISBLANK to try to get the row to turn red, and had some close calls, but for some reason it would highlight a few rows that actually had data in them.

Thanks for the help!

Greg (broscup)

### Conditional Formatting Two Conditions, So close!!

Hey Everyone,

I was wondering if anyone could help me out with this conditional formatting problem I think I am so close!!! I know how to do it as a traditional "if/and" statement, but I just don't know how to input it into the Format Values where this formula is true:
box.

This is the formula which I have if I was just trying to do it 'normally' instead of conditionally formatting it. In plain English, if the value in cell A1(relative) is greater than the value anchored in A34 and The values in Sheet2 in cell A1(reletive) is greater than the value anchored in A34 (also Sheet2), then highlight the cell.

ie.

=IF(AND(A1>=\$A\$34,'Sheet2'!A1>='Sheet2'!\$A\$34),"Highlight","None"))

But how do I format it properly to put into a Conditional Formatting "Use a formula to determine which cells to format" box

Thanks in Advance for any guidance!

No luck finding an answer? You could always try Google.