Conditional Formatting to Highlight Cells in Row if any number is present, but NOT if a formula or blank cell is present

Question

I am looking for a conditional formatting rule that will highlight cells in a given row that contain a number only, but NOT a formula and NOT blank cells.

Example of the sheet:

Number of cases assigned to Agent	Case Agent ID	Case Number
4	1234	2345678
	1234	2236789
	1234	2312759
	1234	2367842
3	5632	2198423
	5632	1948863
	5632	8574337

One cell in Row 1 SHOULD contain a formula like such: "=COUNTIF(B:B,B2)" which then prints the number of cases assigned to the particular case agent ID. However occasionally a new user of the sheet will just type "4" instead of ensuring the formula is entered in the first adjacent cell. However, we don't need all cells in row 1 to list the number of cases assigned to case agent, we just want one cell to contain that number.

Is there a way to use conditional formatting to change the background color of the cells in row 1 where the cell contains a plain number and leaves cells alone that are blank OR that contain a formula that prints a number? The hope is that this color change will prompt users to make sure the formula is present in row 1 instead of just typing the number of cases they just entered into the spreadsheet?

Answer 1

To format cells in A2:A that contain a number but do not contain a formula, ignoring blank cells, use this conditional formatting custom formula rule:

=isnumber(A2) * not(isformula(A2)) * not(isblank(A2))

To prevent entering a plain number in the column in the first place, use this custom formula rule with Data > Data validation for the range A2:A:

=isnumber(A2) * isformula(A2)

Click Advanced options and choose Reject the input to finish the rule.

The rule will prevent entering numbers or text in those cells, and will only accept formulas that produce a number.