none
FORMAT CELLS IF VALUE IS ENTERED IN CELL! RRS feed

  • Question

  • Please if someone could help.  

    What I want is: if a value is entered in cell A50 then underline row from A50 till Q50.

    Row 50 is an example!  I want this to apply from row 5 untill row 10.000

    Thanks,

    Sunday, August 3, 2014 9:31 AM

Answers

  • Select the entire range that you want to format this way (e.g. rows 5:10000).

    I will assume that the active cell within the selection is in row 5. You can see the address of the active cell in the name box on the left hand side of the formula bar.

    On the Home tab of the ribbon, in the Styles group, click Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula   =$SA5<>""

    The $ before the column letter A is essential here - without it, the formula won't work as intended. There should not be a $ before the row number 5.

    Click Format...

    If you want to set the bottom border of the cells, activate the Border tab of the Format Cells dialog, and click on the bottom border of the sample cell.

    If you want to underline the text in the cells instead. activate the Font tab of the dialog, and select Single from the Underline dropdown.

    Click OK to return to the New Formatting Rule dialog, then OK to save the rule.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 3, 2014 10:50 AM

All replies

  • Select the entire range that you want to format this way (e.g. rows 5:10000).

    I will assume that the active cell within the selection is in row 5. You can see the address of the active cell in the name box on the left hand side of the formula bar.

    On the Home tab of the ribbon, in the Styles group, click Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula   =$SA5<>""

    The $ before the column letter A is essential here - without it, the formula won't work as intended. There should not be a $ before the row number 5.

    Click Format...

    If you want to set the bottom border of the cells, activate the Border tab of the Format Cells dialog, and click on the bottom border of the sample cell.

    If you want to underline the text in the cells instead. activate the Font tab of the dialog, and select Single from the Underline dropdown.

    Click OK to return to the New Formatting Rule dialog, then OK to save the rule.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 3, 2014 10:50 AM
  • Works!  Thanks,
    Sunday, August 3, 2014 11:30 AM