Board Regular
Nov 30, 2005
Office Version
  1. 365
  1. Windows
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!


Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try this and let me know if I've missed anything:

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
Upvote 0
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).
Upvote 0
Well confused now!

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

Upvote 0
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.
Upvote 0
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.
Upvote 0
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.
Upvote 0
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"
Upvote 0
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
Upvote 0

Forum statistics

Latest member

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
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 "".
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