# Tag Info

## Hot answers tagged conditional-formatting

3

=sparkline(1,{"charttype","bar";"color1",A1}) This will provide a solid colored cell using the Hex color code in A1. Also accepts some named colors as well, e.g. "green", "yellow".

3

You mentioned How can I use a filter or conditional formatting to find all numbers that are NOT in this format (#### ### ###). Please use the following conditional-formatting rule =IF(A1<>"",(IF(ISNUMBER(A1),TRUE,NOT(REGEXMATCH(A1,"^\d{4} \d{3} \d{3}\$")))))

2

Please try the following formula in Custom formula is =IFERROR(IF(MATCH("*"&INDIRECT("H5")&"*",B2,0)>0,true,),false)

2

First substitute the white space. Paste this to Cell B2: =SUBSTITUTE(A2, " ", "") After that make your number to exactly 10 digit. Paste this to Cell C2: =IF(LEN(B2)=9,"0"&B2, IF(LEN(B2)=8,"00"&B2, IF(LEN(B2)=10,B2))) Lastly add the space back to the right place.Paste this to Cell D2: =LEFT(C2,4)&" &...

2

Please use the following formula =B2>=A2 for green (if the cell on the right is greater than or equal to the cell on the left) =AND(B2>=A2,B2<>"") =B2<A2 for red (if the cell on the right is less than the cell on the left) =AND(B2<A2,B2<>"")

2

Either the Apply to range or the formulas are wrong. Instead of B:B use B2:B OR Instead of B2>=A2 it should be B1>=A1 Instead of B2<A2 it should be B1>A1 Apply to range you might select the whole column B Use a custom formula with relative references i.e. B1 instead or \$B\$1 First rule Second rule Related How to conditional format in ...

2

Addressing strictly what you've requested in your post, I added a sheet ("Erik Help") with a custom conditional formatting rule applied to Column A: =AND(LEN(A1),ISERROR(REGEXEXTRACT(A1,"^\d{4}\s\d{3}\s\d{3}\$"))) This will highlight any entries in Column A that are not in the format "#### ### ###". You can view this rule by ...

1

To conditionally format cell A6, in which we have a date, I used the option under conditional format *Custom formula is* (bottom drop down) and put in the formula =weekday(A6,2)<6 This is for condition on cell A6 If you want to mark the Saturdays & Sundays, use: =weekday(A6,2)>5

1

The closest built-in feature is Conditional Formatting unfortunately it doesn't support cell borders. One option is to use Google Apps Script. Related Automatically add borders around nonempty cells Conditionally format row borders Resources https://developers.google.com/apps-script/guides/sheets

1

Please use the following formula =SEQUENCE(COUNT(B5:B),1,DATE(A2,MONTH(A1&1),1),1) You then format your cells as Tue found under Format >Number >More date and time formats I also noticed that you have the following conditional formatting applied, which will no longer work =OR(\$A5="Fri",\$A5="Sat") What you should now use is =...

1

To one cell A1 only.. it will be highlighted whenever the the formula is true.. What you need is a COUNTIFS formula as your conditional rule for cell A1 =COUNTIFS(\$Q9:\$Q,"=Consultation",S\$9:S,"=")>0

1

Applied to range D1 (or D1:D if you want the following custom CF formula to apply to the entire Column D): =B1+C1=0 Format as desired (i.e., red fill).

1

Instead of \$J\$9:\$P\$9 use \$J9:\$P9 The above reference make referred columns to be absolute but rows are relative

1

@Steven, I've added a sheet ("Erik Help") with the following custom CF formula/rule applied to D4:D73 ... =NOT(ISERROR(VLOOKUP("*"&D4&"*",{C\$116:C\$125;E\$116:E\$125},1,FALSE))) NOTES: NOT(ISERROR(VLOOKUP(...))) basically means that something was found (i.e., if it's not an error when Sheets tries to find it, then it was ...

1

Example: Rules: Rules Format ------------------------------------------------ Priority is 3; Alcohol Red Priority is 3; Beer Red and italics Priority is not 3 None Sheet: A B ------------ Alcohol 3 Beer 4 Alcohol 5 Beer 3 Alcohol 4 Beer 5 Alcohol 3 ...

1

If a payment has not been received on an transaction by the first day of the following month, you want the payment cell to appear red. Assume: Column A = Transaction date Column B = Transaction value Column C = Amount received. Use this formula as a "custom formula" for Column C in Conditional formatting. =and(isblank(C2),today()>EOMONTH(A2,0)+...

1

It is possible to do this by using a custom formula in the conditional formatting. Assuming that you are conditional formatting the cells A1:A, enter this conditional formula: =NOT(ISDATE(A1))

1

You can use the following formula under conditional formatting: =AND(C5<\$B\$2,C5>\$A\$2) (Adjust ranges to your needs)

1

Please use the following rule =countif(\$A\$2:\$A2,A2)>1 (adjust range to your needs)

1

Try this as a custom formula: =if(isblank(\$X\$7)=false,if(countif(F1:H10,"A1"),"colour me",""))="colour me" You said \$X\$7... contains an X or is not blank. In that case, you don't need to test for "X", but just if the cell is not blank. You are looking for a value of "false". the test for "A1" is a simple countif(); there may be more sophisticated methods ...

1

Custom number formatting supports conditionals, but only two of them plus a default format. So, to display millions and billions of dollars, you can use Format > Number > More Formats > Custom number format with something like this: [>999999999.999]\$#.00,,, "B";[>999999.999]\$#.00,, "M";\$#,##0_M Unfortunately, this will ...

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