We’re rewarding the question askers & reputations are being recalculated! Read more.

# Tag Info

2

After continuing to play around with functions, I was able to find a solution! Here is the solution for those trying to attempt something similar: =IF((AND((MOD(A:A,4) = 0),(NOT(ISBLANK(A:A))))), true, false)

1

The formula you need for range A1:A is: =and(A1<>"",counta(B1:J1)<>0)

1

You should use the following formula having an IF statement: =(IFERROR(MAX(SPLIT(D3,"-")),0))>F3

1

Use the custom formula =NOT(ISBLANK(C2))

1

You can use conditional formatting with a custom formula like =NOT(ISBLANK(B2)) across the range you want to use, in this case C2:C60. Make sure not to use any \$ signs and to reference the first cell in your range (in your screenshot there's an offset), it should be B2. Another reason why your set-up may not work is because the top-most conditional ...

1

Two solutions. A/ Either you do a condition for each case =A1>A2 BLUE =AND(A1>0,A1<A2) YELLOW =A1<0 RED B/ You set the cell in yellow and put only 2 condition format rules. =A1>A2 BLUE =A1<0 RED

1

Try REGEXMATCH : =OR(REGEXMATCH(\$B2,"yr"),REGEXMATCH(\$C2,"yr")) Second entry is a JS regular expression. You can build it with a tool like regexr.

1

EDIT (following your comment) All duplicate values highlighted in both columns is achieved using: In range D3:D27: =COUNTIF(\$A\$3:\$A,D3) In range A3:A27: =COUNTIF(\$D\$3:\$D,A3) First approach I believe the formula you need is as simple as: =\$A1="Roman"

1

Actually I found a very simple answer. =COUNTBLANK() and it's working. Thanks anyway

1

You just need to type =k\$11 instead.

1

I have a cell with a check box. I want another cell which contains a date to go green when the tick box is checked. You go to Format, Conditional Formatting and use the Custom formula: =COUNTIF(\$D3, "=TRUE") EXTRA: On the other hand, if you want the whole range/column to be highlighted use the following formula: =COUNTIF(\$B\$3, "=TRUE") Pay ...

1

on your spreadsheet, record any macro and create a keyboard shortcut for it. Then go to Tools->Macros->Manage macros and change the macro code to the following one (you might need to change the function name to the same of your macro). /** @OnlyCurrentDoc */ function xxx() { var spreadsheet = SpreadsheetApp.getActive() do { spreadsheet....

1

I have just been using an online Excel spreadsheet and it is now possible to setup conditional formatting in Online Excel.

1

An alternative and more versatile solution is to select custom formula and use regular expression matching with =REGEXMATCH(A1,"[^\*]\*{2}[^\*]") looking for asterisk sequences of the precise length 2 (to match 1 or 3 replace the number two accordingly).

1

This is totally working for byte formatting (bytes - GB): =if(B23>=1024; if(B23>=(1024*1024); if(B23>=(1024*1024*1024); B23/(1024*1024*1024)&" GB"; B23/(1024*1024)&" MB");B23/1024&" kB");B23&"bytes") Can be used for any other magnitude by just altering the numbers and the string labels. Note: you need an & between the number ...

1

=OR(D\$2="Sat",D\$2="Sun") is the custom formula I use to change column colour if the header (line 2 in the sheet) is Sat or Sun. To use a full word, just type it inside the quotation marks. The dollar sign \$ locks the formula to row 2 over the range. So if you put the Dollar Sign \$ in front of the column letter, it will check the column instead of the row.

1

A custom formula in the Conditional Formatting interface can do this for you. It looks like your dates in Column C are formatted as dates, and not just text. So I'll demonstrate a formula that will work in that case. (If your Column C is just text, you can adjust the formula to look for the strings "Saturday" or "Sunday".) Select Cell D13. Click on the Fill ...

1

If you have your data in, say, A2:A200 (assuming a header row), and you holidays are stored in G2:G20, then set a Conditional Formatting rule with: Apply to range: A2:A200 Custom formula is: =SUMPRODUCT((A2=(\$G\$2:\$G\$20)))>=1 You could also use Apply to range: A2:E to also color columns B:E and to not limit the number of rows. In Excel, using non-array ...

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