Color active cell

[alfonsoto1]

How do i go about coloring an active cell aaaaaaayellow?

 

[DanteAmor]

Activecell.interior.color = vbyellow

 

[alfonsoto1]

I am using the following macro in one work sheet and it works very well, but it won't not work in another worksheet, why?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.ColorIndex = xlNone
    ActiveCell.Interior.ColorIndex = 6
End Sub

 

[DanteAmor]

Delete that macro and put the next one but in ThisWorkbook events, it will work on any sheet.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
ActiveCell.Interior.ColorIndex = 6
End Sub

 

[Bond00]

Delete that macro and put the next one but in ThisWorkbook events, it will work on any sheet.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
ActiveCell.Interior.ColorIndex = 6
End Sub

just curious what makes this one better? what does ByVal Sh As Object do differently?

 

[Peter_SSs]

just curious what makes this one better? what does ByVal Sh As Object do differently?

It works in any worksheet in the workbook (provided it is placed as explained by Dante) - which is what the OP asked for.

@alfonsoto1
Might any of your cells in any of your worksheets already contain colour for some reason? If so this code will wipe that colour out. If you do (or might) have other colours already in the sheet(s), other alternatives are available.

BTW, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

It would also help if you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Bond00]

It works in any worksheet in the workbook (provided it is placed as explained by Dante) - which is what the OP asked for.

Yeah i know the location of the code is the only thing that matters most for what OP needed so it works in all sheets. But just wondering why he made that new code and told him to delete what he already had, wondering what made it better/more efficient?

 

[Peter_SSs]

Yeah i know the location of the code is the only thing that matters most for what OP needed so it works in all sheets.

But location itself is not enough. If the OP's original code was simply moved to the ThisWorkbook module it would not work.

But just wondering why he made that new code ..

As per the above, the original code would only work in the worksheet whose module it was in. The OP's question was how to make it work in all the worksheets.
There would be two ways to do that.

1. Repeat the code in every worksheet module (could be many worksheets), or
2. Use one single code in the ThisWorkbook module

Seems to me a single code in one module is more efficient than codes in multiple worksheet modules. Further, if new worksheets are added the ThisWorkbook code means that no code also has to be also added to the new worksheets.

... and told him to delete what he already had,

If the original code was not removed then there would be a code in that worksheet module clearing colour from all cells in the worksheet and then applying colour to the active cell and there would be code in ThisWorkbook clearing colour from all the cells in the worksheet and then applying colour to the active cell.
Doing the clear and apply colour once is surely more efficient than doing it twice every time the selection is changed. Hence the request to remove the original code.

 

[Bond00]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Oh ok so the 1st one only works in sheets for some reason, thats why he gave this 2nd line that will work in the workbook or a module. got it.

 

[Fuji]

How do i go about coloring an active cell aaaaaaayellow?

To worksheet code module.
This will not change the color of the cell(s) if there is/are already colored.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim cf As FormatCondition, r As Range
    For Each cf In Me.Cells.FormatConditions
        If cf.Type = 2 Then
            If cf.Formula1 Like "=ADDRESS(*" Then cf.Delete
        End If
    Next
    For Each r In Target
        r.FormatConditions.Add 2, Formula1:="=address(row(),column())=""" & r.Address & """"
        r.FormatConditions(1).Interior.Color = vbYellow
        r.FormatConditions(1).StopIfTrue = False
    Next
End Sub