Conditional Format Between First and Last Non-Blank Cells

Question asked by user @ Excel forum:-

How to use Conditional formatting  between first and last non-blank cells?

I need to conditionally format cells that are between first non-blank and last non-blank in column wise...

image 1

User wants to highlight the range from the first non blank cell till the last non blank cell in the columns of data 1, 2, and 3.

In the Data 1, follow below given steps:-

  • Select the range B4:B24
  • Then click on “Conditional Formatting” drop down list will appear, where we have to select “Manage Rules”  dialog box will get open.

image 2

  • Click on “New Rule”, then Select Use a formula to determine which cells to format.
  • Enter the formula in formula tab :- =AND(OR($B2<>"",COUNTA($B2:$B$2)),COUNTA($B2:$B$22)>0)
  • Click on the format tab, select the green color.

 

image 1

Range will get highlighted from where data is getting start and till the last active cell.

 

image 4

 

In the Data 2, follow below given steps:-

  • Select the range C4:C24
  • Then click on “Conditional Formatting” drop down list will appear, where we have to select “Manage Rules”  dialog box will get open.
  • Click on “New Rule”, then Select Use a formula to determine which cells to format.
  • Enter the formula in formula tab :- =AND(OR($C2<>"",COUNTA($C2:$C$2)),COUNTA($C2:$C$22)>0)
  • Click on the format tab, select the Orange color.

 

image 2

  • Range will get highlighted from where data is getting start and till the last active cell.

image 6

 

 

In the Data 3, follow below given steps:-

  • Select the range D4:D24
  • Then click on “Conditional Formatting” drop down list will appear, where we have to select “Manage Rules”  dialog box will get open.
  • Click on “New Rule”, then Select Use a formula to determine which cells to format.
  • Enter the formula in formula tab :- =AND(OR($D2<>"",COUNTA($D2:$D$2)),COUNTA($D2:$D$22)>0)
  • Click on the format tab, select the Yellow color.

 

image 3

  • Range will get highlighted from where data is getting start and till the last active cell.

image 8

 

This is the way we can highlight the range between first and last non blank cells in Microsoft Excel through Conditional Formatting.

 

Main Page

Conditional Format Based on Dates

Find Occurrence of Text in a Column

How to Highlight a row on the basis of Cell

Compare 2 Columns and Return Fill Red if is different

How to check the row and then highlight the first cell of the row

Highlight Cells Tomorrow Excluding Weekend

Conditional Formatting to Mark Dates on a Calendar

How to apply Conditional Formatting  in a Cell before a Particular Character

Highlight the Top 10 Sales through Conditional Formatting

Conditional Formatting for Pivot Tables

Conditional Format Between First and Last Non-Blank Cells

PDF

Excel

 

 

 

Comments

  1. Your relative references are off in the formula. It doesn't work for rows 2 and 3.

    It should be:
    =AND(OR($B2"",COUNTA($B$2:$B2)),COUNTA($B2:$B$22)>0)

    You should ALWAYS test your border conditions!

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.