FORMATTING A ROW WITH A MACRO

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
Hey guys once again!

I am trying to produce a macro button that when pressed will turn a row I am currently in grey. This will be repeated for green, red, orange and white. I can make the button easy enough but the actual macro is proving difficult.

6 sheets long, whichever sheet I am in, I would like to click the appropriate button (easy enough) and the whole row, or even better a selection of my choice, will go that colour. The actual cells I want to change to a particulat colour are 'A' through 'O' of the active cell.

Sounds complicated but the intention is easy!

PLEASE HELP IF YOU CAN!
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148
Try this and let me know if I've missed anything:

Code:
Private Sub cbTest_Click()

    'Cancel if more than one row is selected
    If Selection.Rows.Count <> 1 Then Exit Sub

    With Range("A" & ActiveCell.Row & ":O" & ActiveCell.Row)

    'Toggle the colour - you can add new cases if you want to increase
    ' the number of colours.
    Select Case .Interior.ColorIndex
    Case -4142: 'blank
        .Interior.ColorIndex = 15
    Case 15:    'Grey
        .Interior.ColorIndex = -4142
    End Select

    End With

End Sub
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows
You should be able to use the Macro Recorder to get most of your code. Then you just need to clean it up a little to generalize it.

These little tidbits of info may come in handy:

To change the row color of the selected range to grey:
Selection.Interior.ColorIndex = 48

To change the row color of the active row to grey, but only columns A-P:
Range(Cells(ActiveCell.Row,"A"),Cells(ActiveCell.Row,"P")).Interior.ColorIndex = 48

See how far you can get, and post back if you need more help (and post the code you have so far).
 

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
Well confused now!

rjp: I copied and pasted this but nothing happens? How do i activate or link to the macro?

Thanks
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Did you try my suggestion?

The Macro Recorder will record whatever steps you do. So if you turn on the Macro Recorder, and record yourself manually performing the steps, then stop the recorder, you will have recorded much of the code that you are looking for.

Usually, the code has to be edited at least a little to "generalize" it a bit, but much of what you need will be there.
 

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
I have done it sort of the code I have is as follows:

Sub Macro6()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "P")).Interior.ColorIndex = 48

End Sub


This does the job perfect apart from it change the whole row rather than just A through P.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,432
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You may want to test that again. I ran your code, and it only changes the color of columns A-P, not the whole row.

Are you sure the column wasn't already completely colored?
Pick a blank row, run the macro, and check column Q when you are done.
 

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148
When I wrote my solution I used a command button named cbTest and hence it's click event code is in the cbTest_Click sub-routine, you need to put my code into the place for the click event of your own button, of course don't include Sub cbTest_Click() or End Sub lines.

Looking at your last post I'm guessing you need to put it into Macro6.

If you did this and it didn't work then I'm guessing that the existing colour code isn't one of the 2 that I check for. Either modify the number in my case statement or use Case Else. Check out the Select...Case functionality in the on-line help.

Tip: To find the colour code for a cell, select it in the worksheet then switch to the VB editor and in the Immediate window (Ctrl+G if you can't see it) and type "? activecell.interior.colorindex"
 

mrbeanyuk

Board Regular
Joined
Nov 30, 2005
Messages
212
Thanks for your help guys - yes I was actually looking to only select a few rows. Neway, thanks to you to coding has worked perfectly and eased a long and drooling job:

Sub Grey()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "N")).Interior.ColorIndex = 15

End Sub


Sub Orange()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "N")).Interior.ColorIndex = 45

End Sub

Sub White()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "N")).Interior.ColorIndex = 2

End Sub

Sub Blue()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "N")).Interior.ColorIndex = 8

End Sub

Sub Red()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "N")).Interior.ColorIndex = 3

End Sub

Sub Green()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "N")).Interior.ColorIndex = 43

End Sub

Sub LightBlue()

Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "N")).Interior.ColorIndex = 35

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,967
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top