Highlight rows with a rule you can explain

01Define the exception before selecting a color

Paste the sample into A1:D6 on a sheet named Stock. Reorder means a numeric Stock value strictly below a numeric Minimum. Zero is valid stock; the empty Stock cell for P04 means unreported. Equality is sufficient stock in this exercise, so P05 must not be flagged.

Add Status in E1 and enter the helper formula below in E2, then fill down to E6. The expected statuses are Reorder, Enough, Reorder, Check input and Enough. A readable status makes the rule auditable and useful without relying on color.

=IF(AND(ISNUMBER(C2),ISNUMBER(D2)),IF(C2<D2,"Reorder","Enough"),"Check input")

02Anchor the rule to the first selected row

Select A2:D6 with A2 as the active cell. On Windows choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the rule below, choose a noticeable fill and confirm. In Manage Rules, verify Applies to is =$A$2:$D$6.

The dollar signs lock columns C and D because every cell in a highlighted row must examine that row's stock and minimum. The row number stays relative: row 3 examines C3 and D3. Writing $C$2 and $D$2 would make every row depend on P01 instead.

=AND(ISNUMBER($C2),ISNUMBER($D2),$C2<$D2)

03Test boundaries and diagnose surprises

Confirm the three boundary cases separately: P03 has zero stock and is below five; P04 has unknown stock; P05 has five against a minimum of five. A rule containing only C2<D2 can make a blank quantity look like zero, so the numeric checks matter.

Conditional formatting changes appearance, not the underlying data or total. If a row has the wrong appearance, inspect its status, actual stored values, formula, applied range and other rules. A number stored as text is intentionally Check input here; repair its type rather than loosening the rule. Actual worksheet errors also need diagnosis, not a claim that an uncolored row is correct.

After adding more products, extend both the helper formulas and the rule's applied range. Do not assume a fixed A2:D6 range will cover a newly appended seventh row.