Excel - Macro to highlight adjacent cells colors

Common problems – different language?
Let's talk in English.

Excel - Macro to highlight adjacent cells colors

Poslaťod Palo-admin » 01.12.2009, 17:26

I am in need of assistance with a Private Sub Macro.

I want the macro to run continuously with a Private Sub.

I have three columns. 2 columns have information in them. The
third adjacent column will be entered by the user.

I need the macro to highlight the cells in the adjacent cells red for "O"
yellow for "P" and green for "A" the end user will be entering
letters referenced here.

Thanks for your help

Cathy


Hi Cathy,
There exist more simple way to achieve results you need.
To use conditional formating.

For Excel 2003 and below:

1) Select range of user entry cells
2) Main menu / Format / Conditional Formatting...
3) Cell Value - "equal to" - "O"
4) Press button "Format" to set cells format
5) Press button "Add>>" to set other values (P and A)


Example:
http://www.pc-prog.eu/phpBB3/images/xMyKB/ConditionalFormating.xls

For Excel 2007:
1) Select range of user entry cells
2) Tab "Home" / group "Styles" / Conditional Formating / New Rule
3) Rule type: "Format only cells that contain" /
4) Cell Value - "equal to" - "O"
5) Press button "Format" to set cells format


Example:
http://www.pc-prog.eu/phpBB3/images/xMyKB/ConditionalFormating.zip

But if you need to use Macro:
1) Select range of user entry cells
2) Main menu / Insert / Name / Define: e.g. "UserEntry"
3) ALT + F11 - VBA code window
4) Add such a code into object "Sheet1"


Kód: Vybrať všetko
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo xErr:
    If Not Intersect(Target, Range("UserEntry")) Is Nothing Then
        Application.EnableEvents = False
       
        If Target.Value = "O" Then
            Target.Interior.ColorIndex = 3 ' red
        ElseIf Target.Value = "P" Then
            Target.Interior.ColorIndex = 6 'yellow
        ElseIf Target.Value = "A" Then
            Target.Interior.ColorIndex = 43 ' green
        Else
            Target.Interior.ColorIndex = xlNone
        End If

        Application.EnableEvents = True
    End If
   
    Exit Sub
   
xErr:
     Application.EnableEvents = True
End Sub


Example:
http://www.pc-prog.eu/phpBB3/images/xMyKB/FormatingViaMacro.xls

Palo
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Solving MS Office problems in English

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť