Active Cell to have White Fill

[aellington287]

I have a spreadsheet that is mostly grayed out. I want whichever cell is currently active to have a white fill. Is there a way to do that without VBA, and if not, what line of code would do that?

 

[Jeffrey Mahoney]

I've done that before using VBA in several different ways. This will slow down you navigation, potentially by a lot. Thinking logically, you have a range that's greyed out. Each time you move your cursor, the macro has to grey out all the cells and the white out the current cell. It can be done.

 

[Eric W]

Sadly, you can't do that without VBA. There's no built-in function that you can use to find the active cell. The best I can come up with is to use a Selection Change event to store the row and column of the active cell in some unobtrusive location. Then you can use Conditional formatting to check that location. If that sounds viable, follow these steps:

1) Open the sheet you want to format.
2) Right click on the sheet tab on the bottom and select View Code
3) Paste the following code:

Private Sub Worksheet_Selectionchange(ByVal target As Range)

    [Y1] = ActiveCell.Row
    [Z1] = ActiveCell.Column

End Sub

4) Change the cell references in red to where you want to put the activecell row and column.
5) Press Alt-Q to close the VBA editor.
6) Select the range you want to conditionally format. Let's assume the top left corner of the range is A1.
7) Click Conditional Formatting --> New Rule --> Use a formula
8) Enter this formula:
=(ROW(A1)=$Y$1)*(COLUMN(A1)=$Z$1)
where A1 is the top left corner of your range, and Y1 and Z1 are the cells you picked in step 4.
9) Click Format and choose a fill color.
10) Click OK and try it out.

Even this isn't perfect. It seems a little flaky, especially when you select a range instead of a cell.

Let me know how that works.

 

[Rick Rothstein]

I have a spreadsheet that is mostly grayed out. I want whichever cell is currently active to have a white fill. Is there a way to do that without VBA, and if not, what line of code would do that?

What is the range of cells that are grayed out?

What Color or ColorIndex value are you using for the gray color?

 

[Jeffrey Mahoney]

This will work ok. You'll probably want to change the colors. I named the range that is Grey: "Selection_rng". You can name it anything you want.


Paste this in your sheet module

Private Sub Worksheet_Selectionchange(ByVal target As Range)
  Dim i As Range
  Dim SRng As Range
  
  
  Set SRng = Range("Selection_Rng")
  If LastRng Is Nothing Then Set LastRng = SRng
  With LastRng.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
  End With
  
  
  Set i = Intersect(target, SRng)
  If Not i Is Nothing Then
    With i.Interior
      .Pattern = xlNone
      .TintAndShade = 0
      .PatternTintAndShade = 0
    End With
    Set LastRng = i
  End If
    

End Sub

Paste this in a standard module

Public LastRng As Range

 

[Rick Rothstein]

Assuming your gray color is ColorIndex value 15 and, like Jeffrey assumed, that the gray colored cells have the defined name Selection_Rng, give this SelectionChange event code a try...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.ColorIndex = xlNone
  Application.ReplaceFormat.Interior.ColorIndex = 15
  Range("Selection_Rng").Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Target.Interior.ColorIndex = xlColorIndexNone
End Sub[/td]
[/tr]
[/table]

 

[Rick Rothstein]

Assuming your gray color is ColorIndex value 15 and, like Jeffrey assumed, that the gray colored cells have the defined name Selection_Rng, give this SelectionChange event code a try...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Application.FindFormat.Interior.ColorIndex = xlNone
  Application.ReplaceFormat.Interior.ColorIndex = 15
  Range("Selection_Rng").Replace "", "", SearchFormat:=True, ReplaceFormat:=True
  Application.FindFormat.Clear
  Application.ReplaceFormat.Clear
  Target.Interior.ColorIndex = [COLOR="#FF0000"][B]xlColorIndexNone[/B][/COLOR]
End Sub[/td]
[/tr]
[/table]

Oh, I forgot to mention... instead of changing the selected cell(s) to white, what I did was set the color to nothing so the default grid color (normally white) shows through. If you really wanted the color white, change the red highlighted built-in constant name to the number 0.

 

[Jeffrey Mahoney]

@Rick

Interesting code. I tried it. The response time is about 2 to 3 times slower on my system. I didn't know code existed to search and replace formatting.

Jeff