ExcelBanter - Automate Macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Automate Macro (https://www.excelbanter.com/excel-discussion-misc-queries/17692-automate-macro.html)

Excel XP

I'm wanting to know if there is a way to enter a range of numbers (Example
0-10) and have the background be a certain color.

Example: I enter the number 5 in a cell and then the background would turn
blue and if I entered the number 15 in a different cell then the background
would turn yellow.

I know that I can write a macro that if I enter the number 5 and change the
background color to blue that I can manually run it. I just don't know how
to automate the process.

Any help is greatly appreciated.

Hi Sherry
you have a couple of options:

1) use Format / Conditional formatting if you have 3 or less conditions

for more than 3 conditions -

2) Bob Phillips has an add-in available for download from his site

http://www.xldynamic.com/source/xld.....Download.html

3) John McGimpsey has notes on his site on how to do up to 6 without VBA

http://www.mcgimpsey.com/excel/conditional6.html

4) Use VBA - here's an example:

the following code pasted into the "sheet module" of the sheet - right mouse
click on the sheet tab that you want the conditional formatting on and

choose view / code you should see on the top left of the VBE window your
file name in bold (if not try view / project explorer) and the sheet that

you were on selected ...that's the "sheet module" ...on the right you should
see some white space - copy & paste the code in there -

assuming you want the conditional formatting to work on cell B6

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:

Application.EnableEvents = False

If Not Intersect(Target, Range("B6")) Is Nothing Then

With Target

Select Case .Value

Case 1: Range("B6").Font.ColorIndex = 4

Case 2: Range("B6").Font.ColorIndex = 3

Case 3: Range("B6").Font.ColorIndex = 0

Case 4: Range("B6").Font.ColorIndex = 6

Case 5: Range("B6").Font.ColorIndex = 13

Case 6: Range("B6").Font.ColorIndex = 46

Case 7: Range("B6").Font.ColorIndex = 11

Case 8: Range("B6").Font.ColorIndex = 7

Case 9: Range("B6").Font.ColorIndex = 55

End Select

End With

End If

ws_exit:

Application.EnableEvents = True

End Sub

--- this turns the font of B6 a different colour depending on what value

(between 1 & 9) is entered in the cell.

Hope this helps

Cheers

JulieD

"Sherry" wrote in message
...
Excel XP

I'm wanting to know if there is a way to enter a range of numbers (Example
0-10) and have the background be a certain color.

Example: I enter the number 5 in a cell and then the background would
turn
blue and if I entered the number 15 in a different cell then the
background
would turn yellow.

I know that I can write a macro that if I enter the number 5 and change
the
background color to blue that I can manually run it. I just don't know
how
to automate the process.

Any help is greatly appreciated.

Sherry

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 6 'yellow
Case Is = 2: Num = 10 'green
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 3 'red
Case Is = 5: Num = 46 'orange
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the above code to
that module.

Adjust numbers and colors to suit.

Note: as written the code operates on column A only.

Gord Dibben Excel MVP

On Tue, 15 Mar 2005 07:29:06 -0800, Sherry
wrote:

Excel XP

I'm wanting to know if there is a way to enter a range of numbers (Example
0-10) and have the background be a certain color.

Example: I enter the number 5 in a cell and then the background would turn
blue and if I entered the number 15 in a different cell then the background
would turn yellow.

I know that I can write a macro that if I enter the number 5 and change the
background color to blue that I can manually run it. I just don't know how
to automate the process.

Any help is greatly appreciated.

Julie

The information you provided was very helpful.

Thank you
Sherry

"JulieD" wrote:

Hi Sherry
you have a couple of options:

1) use Format / Conditional formatting if you have 3 or less conditions

for more than 3 conditions -

2) Bob Phillips has an add-in available for download from his site

http://www.xldynamic.com/source/xld.....Download.html

3) John McGimpsey has notes on his site on how to do up to 6 without VBA

http://www.mcgimpsey.com/excel/conditional6.html

4) Use VBA - here's an example:

the following code pasted into the "sheet module" of the sheet - right mouse
click on the sheet tab that you want the conditional formatting on and

choose view / code you should see on the top left of the VBE window your
file name in bold (if not try view / project explorer) and the sheet that

you were on selected ...that's the "sheet module" ...on the right you should
see some white space - copy & paste the code in there -

assuming you want the conditional formatting to work on cell B6

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:

Application.EnableEvents = False

If Not Intersect(Target, Range("B6")) Is Nothing Then

With Target

Select Case .Value

Case 1: Range("B6").Font.ColorIndex = 4

Case 2: Range("B6").Font.ColorIndex = 3

Case 3: Range("B6").Font.ColorIndex = 0

Case 4: Range("B6").Font.ColorIndex = 6

Case 5: Range("B6").Font.ColorIndex = 13

Case 6: Range("B6").Font.ColorIndex = 46

Case 7: Range("B6").Font.ColorIndex = 11

Case 8: Range("B6").Font.ColorIndex = 7

Case 9: Range("B6").Font.ColorIndex = 55

End Select

End With

End If

ws_exit:

Application.EnableEvents = True

End Sub

--- this turns the font of B6 a different colour depending on what value

(between 1 & 9) is entered in the cell.

Hope this helps

Cheers

JulieD

"Sherry" wrote in message
...
Excel XP

I'm wanting to know if there is a way to enter a range of numbers (Example
0-10) and have the background be a certain color.

Example: I enter the number 5 in a cell and then the background would
turn
blue and if I entered the number 15 in a different cell then the
background
would turn yellow.

I know that I can write a macro that if I enter the number 5 and change
the
background color to blue that I can manually run it. I just don't know
how
to automate the process.

Any help is greatly appreciated.

Your response looks close to what I need, slight variance here....Need entire
row to have background fill when the value in Column A is changed from 1 to
another value between 1 and 16. Anyway you could help me with modifying the
formula so that instead of the font changing it is the fill color and instead
of just the one cell within the column it could effect the entire row in
question?

Thank you for any help.
--
Judy Rose Cohen

"JulieD" wrote:

Hi Sherry
you have a couple of options:

1) use Format / Conditional formatting if you have 3 or less conditions

for more than 3 conditions -

2) Bob Phillips has an add-in available for download from his site

http://www.xldynamic.com/source/xld.....Download.html

3) John McGimpsey has notes on his site on how to do up to 6 without VBA

http://www.mcgimpsey.com/excel/conditional6.html

4) Use VBA - here's an example:

the following code pasted into the "sheet module" of the sheet - right mouse
click on the sheet tab that you want the conditional formatting on and

choose view / code you should see on the top left of the VBE window your
file name in bold (if not try view / project explorer) and the sheet that

you were on selected ...that's the "sheet module" ...on the right you should
see some white space - copy & paste the code in there -

assuming you want the conditional formatting to work on cell B6

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:

Application.EnableEvents = False

If Not Intersect(Target, Range("B6")) Is Nothing Then

With Target

Select Case .Value

Case 1: Range("B6").Font.ColorIndex = 4

Case 2: Range("B6").Font.ColorIndex = 3

Case 3: Range("B6").Font.ColorIndex = 0

Case 4: Range("B6").Font.ColorIndex = 6

Case 5: Range("B6").Font.ColorIndex = 13

Case 6: Range("B6").Font.ColorIndex = 46

Case 7: Range("B6").Font.ColorIndex = 11

Case 8: Range("B6").Font.ColorIndex = 7

Case 9: Range("B6").Font.ColorIndex = 55

End Select

End With

End If

ws_exit:

Application.EnableEvents = True

End Sub

--- this turns the font of B6 a different colour depending on what value

(between 1 & 9) is entered in the cell.

Hope this helps

Cheers

JulieD

"Sherry" wrote in message
...
Excel XP

I'm wanting to know if there is a way to enter a range of numbers (Example
0-10) and have the background be a certain color.

Example: I enter the number 5 in a cell and then the background would
turn
blue and if I entered the number 15 in a different cell then the
background
would turn yellow.

I know that I can write a macro that if I enter the number 5 and change
the
background color to blue that I can manually run it. I just don't know
how
to automate the process.

Any help is greatly appreciated.