19
The new Google Sheets allows you to do this, as described here. You have to first enable the new spreadsheets in your Google Driver settings as described in the article. Then you can select 'Custom formula is' from the conditional formatting options and enter any formula (do not forget to add the = prefix!). Cell references without $ prefixes are adjusted ...
15
Use ~ (tilde) to escape + (plus sign), = (equal sign) and also other special characters like * (asterisk) and ? (question mark) that are used as wildcards.
References
Apply conditional formatting rules - Docs editors Help
14
Easy (but a bit tedious!) in new Google Sheets. For Conditional Formatting a new rule is required for each colour, roughly. (One colour can usually be skipped from rules and instead applied with normal formatting - which would be overidden where any of the conditions for CF apply.) So expect to repeat the basics of the following at least five times.
Say the ...
14
Similar to this question, you'll want to use conditional formatting.
Select the cells and select Format > Conditional Formatting
Select "Date is" "today" and select a Background colour
13
Yes.
Use Conditional Formatting with three rules: (Format -> Conditional formatting)
"Date is before" "in the past week" -> red
"Date is after" in the past week" -> green
"Date is" "in the past week" -> orange
This will colour all dates more than a week away in green, all dates coming in the next week orange and the remainder of the dates in red. Empty ...
13
You can use a range as a parameter for the ISFORMULA() function.
e.g. If you want to highlight all cells containing formulas in the range A1:J10, then you can use the formula =ISFORMULA(A1:J10) and apply it to the range A1:J10.
Note that this works with normal ranges (e.g. B2:F30), but not with infinite ranges (e.g. B:F).
12
Google Sheets now supports conditional color scales under Menu "Format > Conditional formatting..." then select the tab "Colour scale".
12
The ISBLANK formula should serve your purpose well.
Create a new rule in the conditional formatting menu. Set the range of your rule to D2:D so that the formatting is applied to column D. Then, in the Condition dropdown menu, select "Custom formula is." In the input field, enter NOT(ISBLANK(C2:C)). (The NOT formula negates the value returned by its argument....
12
Writing a formula that satisfies your criteria is a matter of breaking down what your criteria are and implementing corresponding Sheets functions.
You have established that the formatting should be applied only if a cell is not empty. The EQ function tests whether or not one value (such as a referenced cell's) is the same as another. Since we want to test ...
11
I had been struggling with this for a long time, but finally cracked it:
Use conditional formatting on the column with the dates and type the following as a custom formula:
=or(WEEKDAY(A1)=1,WEEKDAY(A1)=7)
where A1 is the first date in the column.
This will apply the conditional formatting to all weekdays with a value of 1 (Sunday) and 7 (Saturday).
9
Here is what you can do:
RightClick B2 → select Conditional formatting → select Custom Formula is (from the List)
Enter the following formula in the textbox:
=b2>=c2
Check the Background Color and select green color
Click on Add another rule
Select Custom Formula is (from the List)
Enter the following formula in the textbox:
=b2<c2
Check ...
7
Yes! Highlight the cells you want to format. Go to Format > Conditional Formatting.
Once you're in the conditional formatting dialog, choose "Custom Formula Is" from the select box (it's the last option). In the text field type:
=len(XY)>30
X will be the column you're in and Y will be the row you want it to start at. So if you want to check the entire ...
7
Solved with:
=INDIRECT( ADDRESS( 4, COLUMN() ) )
ADDRESS gives me a string like "$A$1" and INDIRECT makes it a cell reference.
7
IMO better without "Text contains".
To avoid the possibility of conflicting rules, clear all conditional formatting from ColumnA (assumed because OP does not say where the data is), select it and apply a Custom formula is of:
=regexmatch(A1,"blue|green|orange|red|white")
Select your formatting and Done.
This will format blue-green as well as blue and Red ...
6
Sooo close (I think!). Please try:
=D3=min($D3:$L3)
=min(D3:L3) returns a value but CF needs a TRUE to trigger. So we check "the current cell" (D3) to see whether its value matches that of the minimum in the range D3:L3. If it does we trigger the formatting.
I say "the current cell" because quite which that is depends on the Range: selection. Here it ...
5
An old thread, but it's worth noting that Google just launched this functionality into Google Sheets. This likely negates the need for external solutions for most users. More info: Google Apps Updates -
Conditional formatting improvements in Google Sheets
5
Insert $ between A and 2 in the formula. As CF 'steps through' to test each cell in the range in turn the range is being automatically adjusted. So when for example testing A9 it is checking for the maximum in A9:A1007, which is indeed 14 - so it formats it.
5
As you (maybe) know, the fact that a method is "deprecated" does not mean it doesn't work - just that it might be removed in the future.
So you're probably not getting an error, but a warning, and your code should work as intended.
That being said, there is a replacement: It seems that setBackgroundColors has just been renamed to setBackgrounds, which it ...
4
If the dates are in column A, select them, then do: Format > Conditional formatting... > Format cells if... > Custom formula and put
=and(isblank(A:A)=false, or(weekday(A:A)=1, weekday(A:A)=7))
Some explanation:
weekday(A:A) returns 1-7, for day of the week, and
or(weekday(A:A)=1, weekday(A:A)=7) returns true, if it's Sunday (1) or Saturday (7).
This ...
4
You can achieve your desired results by putting a tilde, ~ in front of the asterisk as an escape character, if you put the conditional formatting rules in the order listed below.
First, create the one for orange when three asterisks occur using text contains and then specifying ~*~*~*. Select custom to pick an orange background. Then create the one for two ...
4
Conditional formatting: If checkbox ticked in cell A1, change color/strikethrough of cells from B1-F1.
Insert a checkbox from "Insert" tab to A1.
Select the cells (B1-F1) that needs to be of different color/strikethrough when a checkbox in A1 is ticked/ selected.
From tab above, "Format" > "Conditional formatting".
In 'Single color' tab, "Apply to ...
3
This is possible with a script. Go to Tools → Script editor... and paste in this script:
function onEdit(e) {
var cell = e.range.getCell(1, 1);
var val = cell.getValue();
if ((val instanceof Date) && (val.getDay() == 0 || val.getDay() == 6)) {
cell.setBackground("red");
} else {
cell.setBackground("white");
}
}
Save the ...
3
Use the conditional format option in the menu format:
Make sure to select the complete column, when applying the conditional format.
See screenshot from the example file I prepared:
Example file: Conditional Formatting. Try to add some numbers yourself add see the result. Be sure to check the settings under Format> Conditional formatting... in the menu.
answered Apr 18 '13 at 17:50
Jacob Jan Tuinstra
18.2k12 gold badges72 silver badges159 bronze badges
3
In case you need more precise times than in the accepted answer, you can use DATEDIF to specify a certain difference between dates.
Example: format cell A1 if date in A1 is older than 10 days.
Instruction:
Select cell A1
Go to Format/Conditional Formatting
Below "Format cells if..." choose "Custom formula is"
type: =DATEDIF(A1,TODAY(),"D")>10
Only top voted, non community-wiki answers of a minimum length are eligible
