## Hot answers tagged conditional-formatting

2

Actually, yes, it can be done through conditional formatting... Sort of. Use the screenshots below to setup a conditional format that uses a custom number format for cells that are TRUE. You should be able to extrapolate the setup for False = No.
It's important to note that it will only change the apparent value in the cell and not the actual value. If you ...

2

Go to the cell.
Press CTRL+1 to open the Format Cell.
Go to the Number Tab
Under "Category", select "Custom"
Where it says "Type", enter 0## #### ####
This should ensure that you get your leading zero, and that the spaces are added.
Excel also has a format under the "Special" category just for Phone Numbers. If you click that, then click "Custom" it ...

2

Set B2 to
=IF(A2=A3, 1, -2)
and set B3 to
=IF($A2=$A3, B2, IF($A3=$A4, IF(B2>0,3-B2,B2+3), IF(B2>0,-B2,B2)))
and drag it down to B30
(or the last row that contains data, whatever that is).
This will evaluate to a positive number
if this row is part of a duplicate value group
and a negative value if it isn’t
(i.e., if Column A contains a unique ...

2

This works in Excel 2010. I'm not sure if it'll work in Google sheets.
Select the range for which you want conditional formatting.
Add conditional formatting using the following formula: (be sure to change the row from 1 to whatever the first row of your selected area is)
=AND($D1<TODAY()+7,ISBLANK($I1))
Select whatever formatting condition you want ...

2

Your problem occurs because the formula you use looks at the columns with "relative reference". That is, unless you specify that the cells you want to check are always in columns F and G by using the dollar-sign ($), the cells the conditional formatting checks will be offset with the same amount of rows and columns as they are removed from the top left cell ...

2

As I understand your situation, there are three relevant columns:
a column with a completion date (let's say it's in column L)
column M, which may contain the string COMPLETED
a column where you want your output (let's say it's column N)
You use the formula =$M2="COMPLETED", which results in TRUE or FALSE. If I understand correctly, instead of ...

2

Let's suppose the letter (L/F) is in column B, then:
Select the first row.
Open Conditional Fomatting / New Rule.
On type of formula, select "Use a formula"
In the box put =$B1="F"
On the button Format select the format that you want.
With the row still selected use the Format Painter to copy this format to the other rows.

2

In Conditional Formatting, apply the formula =OR(B1="",MATCH("?",1:1,-1)>0) to column A.
The MATCH portion will look for a question mark anywhere in any field in the row, not just a standalone question mark.

2

Well, I made a quick attempt and this and came up with a rough version that works. It probably isn't the best so hopefully someone responds with a more elegant solution, but try this:
=IF(AND($B$2<=$B$1,$B$3<=$B$1),"Good",IF(AND($B$3>$B$1,$B$2>$B$1),"Bad", "Neutral"))
B1 = 0.83 (just my value for budget)
B2 = YTD value entered by user
B3 = MTD value ...

2

Here’s a tricky way (that might be considered elegant).
I’ll assume that < 83 is the “good” condition. Use this formula:
=CHOOSE((D1<83)+(F1<83)+1, "bad", "neutral", "good")
Logical expressions like D1<83 evaluate to one of the Boolean values
TRUE and FALSE, which are numerically equal to 1 and 0, respectively.
By adding ...

2

(1) Copy the column you want to format to an empty worksheet
(2) Select the column, and then choose "Remove Duplicates" from the Data ribbon
(3) To the right of your unique list of values or strings, make a unique list of numbers. For instance, if you have 6 categories to color, the second column could just be 1-6. This is your lookup table.
(4) In a new ...

2

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.

2

There may be neater solutions, but you shouldn't need to concatenate first and last names to make a "FullName" column.
Preview of our output
(the white cells on the right show what output our formula will produce to drive the conditional formatting):
Here's our formula
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
...

2

You can use a conditional format to highlight every other set of unique values.
=MOD(SUM(--(OFFSET($A$1,0,0,MAX(ROW(A1)-1,0))<>OFFSET($A$2,0,0,MAX(ROW(A1)-1,0)))),2)
You can significantly shorten the formula if you have a maximum range you want to apply the formatting over, but it would be less robust.
=MOD(SUM(--$A$5:$A5<>$A$6:$A6),2)

2

Rather than using Specific Text, try using Cell Value and equal to, then enter C for one rule and O for the other (do not enter equal or quotes when entering the letter, Excel does it for you). This will look for the only the letters that match.
Applied to the whole column, this ignores text of the Open / Closed heading.

2

Try this out. Highlight cell C3:D4 (as per example) and add conditional formatting with the following formula:
=AND(ROW(C3)-2=DAY($A$1),COLUMN(C3)-2=MONTH($A$1))
I have tested this and it works as described so let me know if you have any problems.
Note: this formula excludes year as a factor, so 1/1/2000 would highlight cell C3, as would 1/1/2015
Copy ...

2

Use relative formulas ($ or not) and set the Applies to range to the entire row.
Just make sure that the first row in your formula and the first row in your Applies to are the same.
(Screenshots show just some range example. Change to match your exact setup.)

1

You will need a macro in VBA, like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wk As Workbook
Set wk = ThisWorkbook
Dim ws As Worksheet
Set ws = ActiveSheet
CRow = Target.Row
CColumn = Target.Column
If CColumn = X Then
CellValue = Cells(CRow, CColumn)
Select Case CellValue
Case Is < 0
Cells(CRow, ...

1

This is similar to Gary’s Student’s approach. Define the following VBA function:
Function MyRowHidden(ref As Range)
MyRowHidden = Rows(ref.Row).Hidden
End Function
See How do I add VBA in MS Office?
if you need help with that. Now you can use MyRowHidden(cell)
to check whether the row containing cell is hidden.
The way I devised to solve the ...

1

Check if cell is blank AND if today is 14 days away from the date in b1.
Select the B column
Select conditional formatting > New Rule > Use a Formula...
=AND((B1-TODAY()<14),(NOT(ISBLANK(B1))))
Select Fill Style for red

1

I have tested this on Excel 2013:
[Green]"+"0.00%;[Red]"-"0.00%
NOTE: actually my excel is in another language with different decimal separator, so it looks like this:
[Green]"+"0,00%;[Red]"-"0,00%
but I assume yours use the dot as decimal.

1

Use the following formula in the conditional formatting. I assumed row 1 is the header row.
=MOD(ROW(),10)-1=1

1

Select the cells in column A. Create a new conditional format based on a formula (Click Conditional Formattting > New Rule > Use a formula to determine which cells to format).
Use this formula
=COUNTIF($B:$B,A1)
Click the Format button and select a format for example a fill color. Click OK.

1

If I understand what you are asking, you can accomplish this with two conditional format rules which use formulas. You didn't state what version you are using, so you will have to use whatever method you need.
The first rule will use the following formula: =AND(NOT(ISBLANK($L1)), $A1="Flag set") This checks to see that L is NOT blank AND A1="Flag set". If ...

1

Select cell A1 (if you don't, these directions will not work due to the relative reference you create in step #3...which is the key to being able to apply these conditions to all the entire worksheet)
Create a new Conditional Formatting rule of the Use a formula to determine which cells to format type
In the Format values where this formula is true: text ...

1

I've done this before. I wish I could provide code for you but it was long ago and I don't have that code any longer. My data was set up with rows as records and columns as fields. I had specific rows to print at certain times, so I added a column to my sheet that I would put a letter into as a label for when to print. i.e. S for Sales dept, A for Accounting ...

1

No, but you can fake it
You can fake it but it takes some setup. It will work unless you need really big shapes. Here are the steps for creating n different conditional format shapes in Excel 2010.
tl;dr Use 1 extra cell and 1 text box for every condition you want. Each text box is setup to show giant text with huge outlines and shadows, all the same ...

1

The way I always make Gantt chars in Excel is to put a formula in all the cells that results in a character in that cell and then use conditional formatting to format the cell. You can "hide" the characters by making them (in the conditional formatting) the same color as the background.
So, for example, if you have starting week numbers in column B and ...

1

there is all sorts wrong, unfortunately!
ADDRESS gives you the address of the cell, not its value... so you are comparing whether an address is bigger/smaller
You have both checks as >= rather than one greater and one less than
You have a mix of , and ; (but I guess that is probably just editing onto this site!)
To enter the formula:
go into cell ...

Only top voted, non community-wiki answers of a minimum length are eligible