Forum Discussion

TimS2025's avatar
TimS2025
Copper Contributor
Jan 02, 2025

Conditional Formatting on Data Range

Hi

I am trying to set some conditional formatting on a row of data.  The row contains numbers from 1 - 52 with the cells changing based on project dates (so the number won't always be the same in the cell if the template is used for different projects).  Any cell that contains a number including 4, 8, 17, 21, 30, 34, 43, 47 I want to fill the cell in one colour.  Any cell that contains a number including 13, 26, 39, 52 I want to fill in a different colour.  

Can anyone help me with the formula I need to conditionally format this row such that the cells format automatically please ? 

Thanks 

  • Let's say the data are in B2:Z2.
    Select this range. The first cell (B2 in this example) should be the active cell in the selection.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =OR(B2=4, B2=8, B2=17, B2=21, B2=30, B2=34, B2=43, B2=47)

    Click Format...
    Activate the Fill tab.
    Select a fill colour.
    Click OK, then click OK again.

    Repeat these steps, but with

    =OR(B2=13, B2=26, B2=39, B2=52)

    and another colour.

  • Let's say the data are in B2:Z2.
    Select this range. The first cell (B2 in this example) should be the active cell in the selection.

    On the Home tab of the ribbon, click Conditional Formatting > New Rule...
    Select 'Use a formula to determine which cells to format'.
    Enter the formula

    =OR(B2=4, B2=8, B2=17, B2=21, B2=30, B2=34, B2=43, B2=47)

    Click Format...
    Activate the Fill tab.
    Select a fill colour.
    Click OK, then click OK again.

    Repeat these steps, but with

    =OR(B2=13, B2=26, B2=39, B2=52)

    and another colour.

    • TimS2025's avatar
      TimS2025
      Copper Contributor

      Thanks Hans - this worked, really appreciate you posting the solution ! :)

Resources