autofill

Posted by Jaap on July 06, 2001 11:09 AM

Here's one for the dye hards.
When I put in a number in a specific range (ex. b12:h22)then the cell has to color blue. But when I put in number zero ("0") than the cell has to turn white again.

thanks in advance.

Posted by Scott S on July 06, 2001 11:20 AM

Highlight your range, and then go to FORMAT-CONDITIONAL FORMATTING. Choose if cell value is greater than 0 to format it with a blue background.

Posted by Joe Was on July 06, 2001 11:23 AM

Change Background Color Hot-key VB Code

To work this, copy the code below and paste to your Macros. Then select each macro and assign a Hot-key to each with Macro-Options, I used Ctrl-s for the 1st for Select and Ctrl-u for the 2nd for Un-Select. The first Code will ( Light-Blue ) your selected cell when you press Ctrl-s and the second will return the cell color to ( Normal ) when you press Ctrl-u.

Sub Macro1()
'
' Macro1 Macro
' Macro by Joseph S. Was
' Ctrl-s
'
ActiveCell.Select
Application.CutCopyMode = False
With Selection.Interior
.ColorIndex = 34
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

Sub Macro2()
'
' Macro2 Macro
' Macro by Joseph S. Was
' Ctrl-u
'
ActiveCell.Select
Selection.Interior.ColorIndex = xlNone
End Sub

Hope this helps. JSW

Posted by Joe Was on July 06, 2001 11:32 AM

Toggle Cell Color VB Sheet-Tab View code Macro

This code toggles a cell color (Light-Blue) on and off with each click it is a Sheet-Tab, View code, macro!

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("Put the Name of your Named Data Range here!")) Is Nothing Then
If Target.Interior.ColorIndex = 34 Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = 34
End If
End If
End Sub

Do not change the name of this macro or it will fail!
This is not a module level macro, it is a sheet level macro! To install this macro, Right click the Sheet-Tab lable and select "View code" the paste the code on the page. When you click in any cell in your range that cell will change to light blue if it is light blue it will change to no color! JSW

Hope this helps. JSW

Macro1 Macro Macro by Joseph S. Was Ctrl-s

Posted by Joe Was on July 06, 2001 12:02 PM

If condition Change Cell Color VB Code Macro

Sub myAddColor()
' Add color if Range("B12:H22") greater than 0, if 0 remove color.
' By Joe Was
'
Range("B12:H22").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="0"
Selection.FormatConditions(1).Interior.ColorIndex = 8
Range("A1").Select
End Sub

This code changes the indicated range light-blue if the cell value is greater than 0 or text. If the value is 0 the color is re-set to no-color. JSW

Do not change the name of this macro or it will fail!

Macro1 Macro Macro by Joseph S. Was Ctrl-s