Stop Shading to Alternate Rows in Excel from getting deleted when data is added

Noidea1970

New Member
Joined
Mar 5, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that I have automated, using 2 buttons to add information and 1 button to delete the information and all tabs that get added.
(The 1st button is a search for and add gather the files I need, the 2nd button actually adds the information from each file).

To make it easier to read all the information I formatted the workbook with alternating shaded rows.
Using the following method. I went to format as a table, choose my color and size. Then choose all the rows and columns I needed.
Checked "my table has headers", Clicked OK
Than I went to convert to range and ended up with what I wanted, see image 1.

When I add the information the shaded rows go back to all white, see image 2.
Image 3 is the code to add the information to Excel. I have 4 workbooks that I use for 12, 25, 37 and 57 parts.
Excel 1.JPGExcel 2.JPGExcel 3.JPG
How can I stop it from going back to all white when the I formation is added?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Question: Why not leave the data as an Excel Table? It manages the shaded rows properly.

You could use conditional formatting to shade every other row. If you filter the data the shaded rows may not be alternating. Copying and pasting conditional formatting duplicates the CF formulas and requires cleanup over time.

A macro could be created to refresh the shading. That's kinda clumsy IMHO.
 

Noidea1970

New Member
Joined
Mar 5, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
Found a solution using Conditional Formatting
I found some code that would allow me to choose the colors I wanted to shade both rows.
The 1st line of code will do the odd rows and the 2nd line will do even rows.
The code will only shade rows that have data in them, or as you put more data in, it will keep expanding.
=MOD(ROW(),2)*($A1<>"")
=(1-MOD(ROW(),2))*($A1<>"")
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Yes, that was what I was going to suggest if you chose that route. I was once resistive to Excel Tables, now I find them very useful. Conditional formatting will show correctly until you hide rows.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,721
Members
416,130
Latest member
galgozzi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top