Conditional Formatting with Macro

Posted by Greg Collett on September 27, 2001 12:02 AM

I want to be able to set conditional formatting in a cell by using the Selection Change subroutine. I have seen the very useful version on this site which checks for changes in a column. I want to be able to check for changes in a particular cell.

I know that you can set conditional formating through the regular menu. However, users can copy and paste cells over the formatted cells thus loosing the settings.

I would very much appreciate it if someone could write the sample code. I will exchange this for another clever trick I have with buttons!

Many thanks

Posted by Robb on September 27, 2001 2:57 AM

Greg

Try this code - it's written for Sheet2 and tests tests changed values in A12.

- Right click on the sheet tab
- Select 'View Code'
- Paste this code in the code window
- If A12 is less 1 or less, or greater than 10, the cell changes colour (or changes back if it is OK)

Test it and, if it does what you want, amend addresses, sheet names etc to suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet2")
If Not Application.Intersect(Target, .[A12]) Is Nothing Then
v = .[A12]
If v > 1 And v &LT;= 10 Then GoTo OK 'Meets conditions
Else
End If
End With
With Target
.Interior.ColorIndex = 36
End With
Exit Sub
OK:
Target.Interior.ColorIndex = xlNone
End Sub

Any help?

Regards

Posted by Greg Collett on September 27, 2001 3:49 AM

Thanks for the fix, Robb

I do a lot of VBA design and have fiound that you can save buttons by using the same one for sequential events. Lets say you use a button to hide some rows, the only step which can follow is to unhide the rows. So...all you have to do is chnge the text on the button from Show Data to Hide Data and attach a new sub routine. You can also change the button text, colour etc at the same time.

Set this sample up as follows:
Open a new workbook
Create a button from the Forms menu and give it a a name called MyButton

Copy and paste the code into a new module, assign Macro 1 to the button and away you go.

Sub Macro1()

' Select the button
ActiveSheet.Shapes("MyButton").Select
' Change the text
Selection.Characters.Text = "Hide"
' Change the formatting of the text
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
'Assign the new macro
Selection.OnAction = "Macro2"
'Select something on the sheet
Range("G3").Select
End Sub
Sub Macro2()

' Select the button
ActiveSheet.Shapes("MyButton").Select
' Change the text
Selection.Characters.Text = "Show"
' Change the formatting of the text
With Selection.Characters(Start:=1, Length:=20).Font
.Name = "Arial"
.FontStyle = "BOLD"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 6
End With
'Assign the new macro
Selection.OnAction = "Macro1"
' Select something else on the sheet
Range("G3").Select
End Sub

Posted by Robb on September 27, 2001 4:12 AM

' Select the button ActiveSheet.Shapes("MyButton").Select ' Change the text Selection.Characters.Text = "Hide" ' Change the formatting of the text With Selection.Characters(Start:=1, Length:=9).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With 'Assign the new macro Selection.OnAction = "Macro2" 'Select something on the sheet Range("G3").Select