Excel question

  Ray5776 18:26 20 Oct 06
Locked

I have recently been learning some basic Excel and can now do most of my requirements but I am beaten by this one, can`t find it in the book or help files.
Although it`s probably there somwhere.

On the bottom row of a spreadsheet I have some cells that are highlighted with coloured text, coloured background or both.
When I add another row under this (now becoming the new bottom row) I want it to have the same format.
I can do this bit but also would like what was previously the bottom row to revert back to the same format as the row above it.
I can change it manualy one cell a time but would think there is a formula to make it automatic.

Can anyone help on this please if I have explained it clearly enough.

Ray

  VoG II 18:34 20 Oct 06

In your new last row is there anything that distinguishes it from the rows above (e.g. today's date)?

  Jackcoms 18:38 20 Oct 06

Am I correct in thinking that you want to remove the coloured background and coloured text and revert to 'plain' in what is now the next-to-bottom row?

If so, click on the row number at the extreme right-hand side - this will highlight the entire row.

To remove the highlighted background, click on the 'Fill colour' icon and choose 'No Fill' to remove coloured text click on the 'Font colour' icon and choose 'Automatic'.

  VoG II 18:59 20 Oct 06

Actually I've thought of an automatic way. This assumes that all columns have the same number of rows and there is something in Column A.

1. Remove the existing formatting.

2. Press ALT + F11 to open the Visual Basic Editor.

3. Insert > Module.

4. Copy and paste in the following code:

Function lastrow() As Long
Application.Volatile
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
End Function

5. Close the VBE using the X.

6. Click in A1

7. Format > Conditional Formatting.

8. In the left hand box click the down arrow and select 'Formula is:'

9. In the next box enter

=ROW()=lastrow()

10. Click the Format button and on the Font tab click the colour to be used for text.

11. Click OK.

12. Click Add. In the conditions boxes enter exactly the same as before (Formula is and the formula).

13. Click the Format button and on the Patterns tab select the cell shading that you want. Click OK twice.

14. Click the Format Painter toolbar button ('paintbrush')

15. Click the small grey square to the left of A and above 1.

16. The entire sheet should now be selected and only the last row should be formatted.

NOTE: you may notice a delay between entering data and the formatting being applied.

  Jackcoms 19:02 20 Oct 06

Bloody hell!

Bookmarked. ;-))

  VoG II 19:23 20 Oct 06

Actually I've thought of an automatic way. This assumes that all columns have the same number of rows and there is something in Column A.

1. Remove the existing formatting.

2. Press ALT + F11 to open the Visual Basic Editor.

3. Insert > Module.

4. Copy and paste in the following code:

Function lastrow() As Long
Application.Volatile
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
End Function

5. Close the VBE using the X.

6. Click in A1

7. Format > Conditional Formatting.

8. In the left hand box click the down arrow and select 'Formula is:'

9. In the next box enter

=ROW()=lastrow()

10. Click the Format button and on the Font tab click the colour to be used for text.

11. Click the Patterns tab, select the cell shading that you want. Click OK twice.

12. Click the Format Painter toolbar button ('paintbrush')

13. Click the small grey square to the left of A and above 1.

14. The entire sheet should now be selected and only the last row should be formatted.

NOTE: you may notice a delay between entering data and the formatting being applied. To reduce the delay, instead of 13, just select the maximum area that you expect your data table to occupy. Otherwise Excel is having to calculate the formula for 256 columns x 65536 rows.

  Ray5776 19:56 20 Oct 06

Jackcoms, my sentiments entirely.

Vog, Thanks for your reply, I will give that a try, I was expecting it to be something complicated, did not realise it was as simple as that:-)
I am joking of course, shall plough through it and get it working hopefully.

Ray

  Ray5776 20:01 20 Oct 06

Jackcoms,
Thanks for your posting and I can and may well do as you suggested but I am going to give Vog`s suggestion a try, I like a challenge.

ray

  Ray5776 21:23 20 Oct 06

I have tried your suggestion Vog but must have done something wrong, I ended up with larger than normal text and no colours. In the original I had the font set at 16 for A1 only it now seems to apply to the whole sheet.

  VoG II 21:30 20 Oct 06

See 1. Remove all formatting.

  Ray5776 21:40 20 Oct 06

I took 1, to mean remove all formatting in the row not the whole sheet, will do that next.
thanks
ray

This thread is now locked and can not be replied to.

What is ransomware and how do I protect my PC from WannaCry?

What I learned from my mentor, Oscar-winning VFX supervisor Phil Tippett

Siri vs Google Assistant