Macro to copy cell format

[Janso]

Hi,

I'll explain the purpose of my spreadsheet before I say what I'm trying to achieve as it might make more sense that way.

I've been asked to create a spreadsheet to log when we have cancelled a sales order and why, but in such a way that it will be a visual checklist. I know that there's a checkbox in excel but I don't want to use that if I can avoid it.

So, this brings me onto my macro question. I want to have two buttons on each row, one that I can click to mark that we have cancelled an order and will mark the range of cells on this row in orange (arbitrary colour choice but thought it made sense since that's half the job). I also want one that will then allow me to click to say we have cancelled the raw material order too, which I want to colour in green.

How do I go about doing this? I've tried following a few guides on how to set up macros but I think this might be a bit more advanced than the things I'm looking at. Is it even possible to do this without massive amounts of faffing about?

 

[Norie]

Would these buttons be in specific columns?

 

[Janso]

Would these buttons be in specific columns?

Yes, every row will be the same number of columns before the buttons and they will be in the same columns on every row.

 

[Janso]

Bump.

 

[Fluff]

Rather than adding a shed load of buttons to your sheet, how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.column = 10 Then
      Intersect(Me.UsedRange, Target.EntireRow).Interior.ColorIndex = 46
   ElseIf Target.column = 11 Then
      Intersect(Me.UsedRange, Target.EntireRow).Interior.ColorIndex = 43
   End If
   Cancel = True
End Sub

Place this in the relevant sheet module.
Then if you doubleclick col J or col K the row will change colour.

 

[Janso]

Rather than adding a shed load of buttons to your sheet, how about

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.column = 10 Then
      Intersect(Me.UsedRange, Target.EntireRow).Interior.ColorIndex = 46
   ElseIf Target.column = 11 Then
      Intersect(Me.UsedRange, Target.EntireRow).Interior.ColorIndex = 43
   End If
   Cancel = True
End Sub

Place this in the relevant sheet module.
Then if you doubleclick col J or col K the row will change colour.

I've tried this but it gives me an error saying that it's unavailable or macros may be disabled - they definitely aren't disabled so I'm not sure what I've done.



I'm not too good with excel so I've probably done something stupid or missed something obvious...

 

[Fluff]

Go into trust centre settings & check that the folder the workbook is in , is in the list of trusted locations.

 

[Janso]

It is in a trusted location, which is what's puzzling me.

 

[Mentor82]

Hi,
Try this: close the workbook with the vba code, open the workbook again and once it opens you should have a yellow bar above the cells saying that macros have been disabled abd there should be a button next to it named "enable content". Press the button and save changes ctrl+s and it should work.
Regards,
Sebastian