Free Microsoft Excel 2013 Quick Reference

- Conditional Formating block cells
- Conditional formatting on blank cells
- Conditional formatting - recognizing text versus number
- Format Multiple Cell Color
- "Offsetting" a conditional format
- Conditional formatting is set with a formula, but now I need to ch
- Conditional Formatting on calculated text
- Need more than 2 conditions in Conditional Formatting
- BULK Conditional Formatting - by column without going into each cell?
- Conditional formatting row when cell value is an even number
- Conditional Formatting if cell content is a formula
- Can a function return a Null (blank ) value? Maybe a custom functi
- Conditional Format Not Working
- Format text in one cell based on value in another cell
- Conditional Formatting and Cell Color
- Conditional formatting doesnt work when I protect the worksheet
- Format text in one cell based on value in another cell
- VBA based conditional formatting (>3 formats needed)
- Conditional Format Percentage
- Conditional formatting multiple conditions (arrays?)

My problem is i have a range B1:B10 with conditional formating, depending on the value of A1 cell. IF A1 = 1 then cells color in range will be draw in white; if A1 = 0 then range will be formated to black color.

But i also want that the cells be deactivated for the people don't can write nothing when the conditional formating is black; and the cells deactivated when the cells is white.

I hope someone can help me, I appreciate to much.

Thanks

Jorge

I have tracking sheets where I want to shade values of a 0 as orange, below 0 as red, and greater than 0 as green.

Thing is, I want cells with no value in to be unshaded. But the conditional formating treats the blank cells as GREATER THAN 0. Why??? Why is a null greater than 0?? Also if I replace the null with a space it still treats a space greater than 0!!

I don't want the blanks shaded green, as I want people to scan down the list and easily pick out those above/below. and there are too many randomly placed blanks (in multiple columns) to remove conditional formatting from the blanks.

I can usually find a way around it by using a "between 0 and 100000" or something like that, but it usually take some fiddling to get it to work and it just seems stupid to me that it treats a null and a space as greater than 0.

Is there a reason for this??

My goal is that:

if projects are "Closed" (non-numerical text should default to white);

if project is overdue (numerical cell value >0), red;

if project is due within 30 days (cell value between 0 and -30), yellow;

if project is not due for 31+ days, green.

The numbers are based off a formula which looks at the due date versus today() to calculate a numeric value for # days overdue.

My conditions are:

1. Cell value is greater than 0 (red)

2. Cell value is between 0 and -30 (yellow)

3. Cell value is less than or equal to -31 (green)

Any suggestions would be greatly appreciated. I do not know how to use VBA so I am trying to do this with formulas and/or formatting.

Thank you.

Erica2261

Condition 1 is formatted with "Cell Value is", Between, ="30 DAYS REMAINING", ="11 DAYS REMAINING"

Condition 2 is formatted with Cell Value is", less than or equal to, ="10 DAYS REMAINING".

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Another cell ,D13, formated in a similar way, except, this particular cell is has a formula to count down the number of days base on another cell, B13.

Ex. If B13 says "1st class", than D13 will count down the number of days remaining. Once D13 reaches 0 days remaining, than B13 changes to say "2nd Class", and D13, will display the number of days remaining. Once D13 reaches 0 days remaining, than B13 changes to say "3rd Class", and D13, will display the number of days remaining.

When D13 displays between 30 - 11 DAYS REMAINING, D13 stays yellow no matter how many days are remaining.

In the conditional format,

Condition 1 is formatted with "Cell Value is", Between, ="30 DAYS REMAINING", ="11 DAYS REMAINING"

Condition 2 is formatted with Cell Value is", less than or equal to, ="10 DAYS REMAINING".

What seems to be the problem?

Plan B is to use VBA on this.

Thanks.

this:

cell I93 conditional format is =$D$90

I'm stumped on conditional formatting for a comparing 2 columns of

text values per row.

So ....

Current Compare Contract#

Name To

A B C

Smith Smith 123456

Jones Greenburg 111222

Johnson Johnson 222111

Column B, Row 2 should be Highlighted

Here's the conditional formatting I've applied:

FormulaIs = B1A1

& then paste the formula down column B

This has the effect of highlighting every cell, where it seems to

evaluate to true all the time. Try this with different variations of $

$$$ & it's about the same effect.

Two things to mention:

1.) I use this same method on a column with Number values & it works

perfectly

2.) The names in column B are based on the following formula:

=INDEX(PI_Last,MATCH(B2,Contract,0))

Where PI_Last is a named range of text (names) on another sheet in the

workbook and Contract is a named range of text values on the current

spreadsheet (Col C). I'm trying to compare where the names are

different for same contract between the two spreadsheets.

Thanks in advance for help.

work around it?

I have a range of b4:b35 and I need to highlight cell different colors

depending on cell value..

If cell =0 then fill with red

if cell =1 then fill with yellow

if cell = 2 then fill with green

if cell = 3 then fill with blue

if cell =off then fill with grey

so as of right now I need 5 conditions unless oen of you wonderful experts

out there have an alternative solution for me....

thank you in advance

another cell's value (eg. set the conditional formatting to "formula

is" and then "=A1>0" and set the colour as red / blue / whatever...),

however wondering if I can do this for an entire column without

individually changing the conditional formatting for each cell one by

one (as there are over 400 rows).

Basically I need a formula that reads the contents of column B for the

particular row that is active.

Can anyone help?

--

Rob Moyle

------------------------------------------------------------------------

Rob Moyle's Profile: http://www.excelforum.com/member.php...o&userid=30432

View this thread: http://www.excelforum.com/showthread...hreadid=500983

was working on.

I have a spreadsheet with a column of numbers... the rest of the data is

text or dates. What I am trying to do is shade the row that contains an even

number in the column that contains the numbers.

I've been able to shade the cell that contains the even number by choosing

only the column with the numbers, and using the following in the conditional

format:

=MOD($A1,2)=0

However, when I try to select all and apply the exact same formula (thinking

it would only look at column A)... the entire spreadsheet gets shaded.

I'm very new to conditional formatting - any ideas?

Thanks!

rather than the value.

I have a spreadsheet that has empty cells, cells with values of 1 and 2, and

cells that will have values of 0 based on 1-1 or 2-2. I do not want to

highlight the blank cells, I only want to highlight the cells that contain a

formula, even if the formula's value is 0. How can I do that? "Cell Value"

looks at the result of the formula and highlights all blank cells. I cannot

figure out what formula to use to look at the cell content rather than the

cell value.

For example:

Cell A1 is blank -- no highlight

Cell A2 value is 1 -- no highlight

Cell A3 value is 0 because formula is =1-1 -- highlight.

I only want to highlight the last cell because is contains a formula.

Thanks in advance!

I have hit the problem in Excel, that any function that references a blank

cell destroys the "blankness" of the result. You can convert Null (a blank

cell) to zero, or an empty string, but these are not the same as blank!

In the original source range, I can use a conditional format of "Cell value

is not 0" (bizarrely, but it works) to highlight cells that contain any

number or text. I can also create subtotal lines and use the SubTotal count

function SubTotal(3, [Range]) to count the nonblank cells.

However if I make a duplicate copy of the range on another sheet using any

function, the function result is always converted to zero (which screws up

the subtotal count) or an empty string (that screws up the conditional

formats).

I tried writing a custom "Keepblank()" function, but even here if the

function returns a null value Excel is converting this to a zero.

This has to be an FAQ. Is there a way with functions, or do I have to write

VBA code to bulk-copy the range to retain the blank values?

encountered a bizarre problem with applying it in this instance.

I want to compare 2 values in the same row (but different columns obviously)

to see if they are the same or different and change the background color of

the cell. I am using OFFSET and indirect functions to find the correct cells

to compare.

If I put the same formula in the conditional format statement and in the

cell itself, the conditional format is not changing the color when the

condition changes to true. The cell is calculating TRUE or false correctly

but no change in formats. (Yes, I have setup the change of formats in the

conditional format window)

Long term, I don't want to have the formula in the cell as well as in the

conditional format because I want the cell value to be able to be anything

while the format flags the changes for me.

I have tried switching from 1 worksheet and back. Recalcing, closing and

reopening and it still doesn't work.

For reference here's some more detail.

The formula in both conditional format and cell....

=INDIRECT($B9)OFFSET(C$1,ROW(INDIRECT($B9))-1,relative_base-scenario)

C$1 is the top of the column this formula is in.

Cell B9 contains the name of a row where the data to compare is located

relative_base is a named row that contains the column number of the scenario

I want to compare to

scenario contains the column number of the scenario I am in

To make things weirder, the above formula always has the conditional format

on while the formula below (same basic calculation just formated differently)

always has the conditional format off.

=OFFSET(C$1,ROW(INDIRECT($B9))-1,0)OFFSET(C$1,ROW(INDIRECT($B9))-1,relative_base-scenario)

What am I doing wrong or what quirk of Excel have I found? Thanks for any

help.

Ken

PS Does Excel 2003 have the conditional format limits (2050 rows) or is that

limitation gone now?

the value of cell A4>=0 and to red if the value of cell A4

I want to color multiple cells based on certain criteria. For example,

I will select all cells F1:F12 and if the percentage in each cell is

less than the percentage in the same row (different column), color that

cell red. If greater, color it yellow. If =, color it white. I used

conditional formatting, and it is screwing up the colors based on what

I'm sure is my crappy logic. Please revise if you can. Thanks,

=IF((F12>C12), 1, 0) - Formatting color chosen is yellow

=IF((F12

Is this something I'm doing when protecting the sheet or is it the code that needs adjusting (and can it be adjusted??). (Please note I am a VBA newbie so adjusting my current code rather than giving me instuction on what to do would be really helpful )

Any help on this would be appreciated

Thanks

Chris

VB:Range) Dim keyRange As Range, tgt As Range Dim oneCell As Range Dim iColor As Integer Set keyRange = Range("C66:F66") Set tgt = Target On Error Resume Next Set tgt = Application.Union(tgt, tgt.Dependents) On Error Goto 0 If Not Application.Intersect(tgt, keyRange) Is Nothing Then For Each oneCell In Application.Intersect(tgt, keyRange) Select Case oneCell.Value Case 71 To 152 iColor = 4 Case 1 To 70 iColor = 43 Case -70 To -1 iColor = 46 Case -152 To -71 iColor = 3 Case Else 'Whatever etc End Select oneCell.Interior.ColorIndex = iColor Next oneCell End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

the value of cell A4>=0 and to red if the value of cell A4<0? I know how to

do conditional formatting on cell A4, based on the value of cell A4, but how

do you format one cell based on the value of another?

Thanks in advance for your help!

Liz

I'm looking to conditionally format the cell colour of an entire row based on text entered in the last cell of the row. Excel's built-in conditional formatting is beautiful for this but I need six different formats (and three seems to be the max).

I did a search and found this helpful thread and I'm trying to adapt the code to my needs.

Here's what I've got so far.

Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Dim RowRng As Range Dim iColour As Integer MsgBox "Macro has been executed!!!!" Set RowRng = Range(Rng, Range("Rng").Offset(0, -9)) For Each Rng In Target If Not Application.Intersect(Rng, Range("J1:J100")) Is Nothing Then Application.EnableEvents = False Select Case Rng.Value Case Is = "ahead" iColour = 3 Case Is = "on time" iColour = 45 Case Is = "behind" iColour = 50 Case Else iColour = 0 End Select If iColour = 0 Then RowRng.Interior.ColorIndex = xlNone Else RowRng.Interior.ColorIndex = iColour End If End If Next Rng Application.EnableEvents = True End SubAt one point in time, when I had set RowRng to a static range, the macro would successfully change the row colour whenever I changed a cell in J1:J100.

I then set out to modify my code to change only the row of the selected cell in the J column and came up with this part.

I was getting an error at this line, but now all of a sudden for some reason, the macro isn't even getting executed when I enter text in the J column of the workseet. I know this because it isn't showing my message box when I change a value in the J column.

What gives??

How can I conditionally format a cell based on the percentage value of

another cell rather than the value of its own cell?. All values ar

Example, I wish to format "A1" based on the value of B1. If B1 = -5% then

A1 would colour Amber, if B1 = -10% then A1 would colour Red, if B1 => 0

then A1 would go Green.

Three format to apply -

(1) B1 => 0% [Green]

(2) B1 <= minus 9.99% [Red]

(3) B1 between minus 0.01% & -9.99% [Amber]

Can this be done, possibly in one formula for conditional formatting?

Thanks,

Mickey

I have always worked with conditional formatting on a basic basis. However, I'm trying to work with more advanced rules and I'm having trouble definining how I should approach it.

Currently on my spreadsheet I have conditional formatting on B18, which is dependent on a tier - 1-5 which is calculated in cell E20.

So,

if E20 = 5 then condition formatting B18 0-104% = green, 104-105% = yellow, 105+ red

if E20 = 4 then condition formatting B18 0-119% = green, 119-120% = yellow, 120+ red

if E20 = 3 then condition formatting B18 0-124% = green, 124-125% = yellow, 125+ red

if E20 = 2 then condition formatting B18 0-129% = green, 129-130% = yellow, 130+ red

if E20 = 1 then condition formatting B18 0-134% = green, 134-135% = yellow, 135+ red

I really don't know how to set conditional formatting arrays, how exactly can I do this, I assume it has to be done with formulas, and not the conditional formatting GUI. Or does this need to be done in VBA? I really appreciate any help, I'm a little out of my experience here.

Now my experience with conditional formatting in VBA is limited - I only know basic conditional formatting in VBA such as..

Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 End Select Target.Interior.ColorIndex = icolor End IfHow exactly do I specify IF values based on another forum. I've used the LOOKUP formula, but I don't think that is applicable here.