Color active cell

[alfonsoto1]

How do i go about coloring an active cell aaaaaaayellow?

 

[Fuji]

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

Missed this one
To ThisWrokbook code moodule.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cf As FormatCondition, r As Range
    For Each cf In Sh.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

 

[Peter_SSs]

Missed this one
To ThisWrokbook code moodule.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim cf As FormatCondition, r As Range
    For Each cf In Sh.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

I would definitely not use that code!

1. If a large range is selected it causes my Excel to stop responding. After all, with say 5 columns selected there would be over 5,000,000 individual cell conditional formats to apply! (And then when something else was selected there would be over 5,000,000 individual cell conditional formats to delete)
   
   
2. With my test sheets if I already had some existing conditional formatting applied, this code altered the colour of my existing CF to the yellow.
   
   
3. The code results in the loss of the ability to use Excel's Undo function.

Point 1 above is actually not too much of a problem because the OP actually asked for the Active Cell to be highlighted not the Active Selection. So that code could be modified to just the Active Cell - but the issues in points 2 and 3 would still exist.


My suggestion is this, which overcomes all 3 of the above points.

A. Remove any codes that you have been trying in relation to this task from the ThisWorkbook module or individual sheet modules

B. For one of the relevant worksheets, select the whole worksheet (by clicking the box at the top left at the intersection of the column labels and row labels). We could select a lesser range but applying it to the whole worksheet doesn't seem to make files too big or slow in my experience.

C. Use Conditional Formatting -> New rule.. -> Use a formula to determine ... -> Format values where this formula is true: =AND(CELL("col")=COLUMN(),CELL("row")=ROW()) -> Format... -> On the Fill tab select the colour you want** for the Active cell -> Ok -> Ok

D. Repeat steps B and C for each worksheet where you might want the Active Cell highlighted. If there is a lot of sheets, a code could be written to do this as a once-off task.

E. Copy and Paste the code below into the ThisWorkbook module

F. Close the Visual Basic window & test.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

** When choosing a colour, better to choose a colour that is not already used on the worksheet. On the Fill tab in the CF set-up if you go to 'More Colors...' it would be easy to make a colour unique for your sheet. With my method above, if it would help, you can use different colours on different worksheets for the Active Cell highlights. For example if you had multiple 'Income' type sheets you might want to use a shade of green and for 'Expense' type sheets you might want to use a yellow etc.

 

[Fuji]

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

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



	
	

		

	

 

[alfonsoto1]

have tried them all,t none of them work. What i do not understand is why i use the same code from a working sheet to another worksheet it does not work

 

[Peter_SSs]

Here is a link to a sample file. Try downloading that file and test. (Sorry, did not spell you user name correctly when naming the file. )
alfonsoro1.xlsm

 

[DanteAmor]

have tried them all,t none of them work. What i do not understand is why i use the same code from a working sheet to another worksheet it does not work

You tried the code from post #4.

I'll put it back to you.

But first you must delete your code and put the following code, but in thisworkbook events

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

ThisWorkbook EVENT
- Open the VB Editor (press Alt + F11).
- Over in the Project Explorer, double click on ThisWorkbook.
- In the white panel that then appears, paste the above code.

 

[Peter_SSs]

BTW, you did not address this question.

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

One further question. Do you have, or might you have, any Conditional Formatting at all (not necessarily only for cell colour) in any of the worksheets in the workbook?

 

[Fuji]

have tried them all,t none of them work. What i do not understand is why i use the same code from a working sheet to another worksheet it does not work

The code I posted should be paced in ThisWorkbook code modue.
Whle you are in vbe, double click on ThisWorkbook from Project Explorer and paste the code.