Fill the color while using if function RRS feed

  • Question

  • Hello Team

    We require formula from cell no B5 to B25 

    A5 Cell have certain date and  B4 cell have also certain date what is required here for macro

    (B4 - A5) < 365 = red color should be filled

    else blue color

    loop next

    (B4 - A6) < 365 = red color should be filled

    note B4 is fixed cell and A column keep changing until the date is available in cell.

    pls note I need to fill the color only and formula should be build within the macro and decide the color


    Wednesday, June 26, 2019 10:24 AM

All replies

  • You don't really need VBA for this, you could use conditional formatting:

    • Select B5:B25. I will assume that B5 is the active cell within the selection.
    • Set the fill color to blue (this will be the default).
    • On the Home tab of the ribbon, select Conditional Formatting > New Rule...
    • Select 'Use a formula to determine which cells to format'.
    • Enter the formula   =$B$4-A5<365
    • Click Format...
    • Activate the Fill tab.
    • Select red.
    • Click OK, then click OK again.

    Regards, Hans Vogelaar (

    Wednesday, June 26, 2019 12:54 PM