Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shading cells with If statements
Does anyone know how I can shade certain cells in by typing in a letter or
number, using If statements? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shading cells with If statements
Try following in the worksheet module (right-click sheet tab view code)
Private Sub Worksheet_Change(ByVal Target As Range) Dim idx As Long On Error GoTo errH If Right$(Target(1), 2) = "##" Then idx = Val(Target(1)) If idx < 1 Or idx 56 Then idx = xlAutomatic Application.EnableEvents = False Target(1).Interior.ColorIndex = idx Target(1).ClearContents End If errH: Application.EnableEvents = True End Sub Record a macro while formating cells with your favourite colours to get colorindex's. Type in your colorindex followed by "##". To clear colour format simply type "##". (Obviously you could amend "##") If you want this to work on all sheets, put similar code in the "ThisWorkbook" module but you need to change the event as follows - Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) 'code End Sub Regards, Peter T "haifa" wrote in message ... Does anyone know how I can shade certain cells in by typing in a letter or number, using If statements? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shading cells with If statements
Why don't you just use the
* Menu - Format - Conditional Formatting and * set "Cell value is" "equal" "A" * Hit the format button and choose whatever text/cell formatting you want "haifa" wrote: Does anyone know how I can shade certain cells in by typing in a letter or number, using If statements? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shading cells with If statements
change
If idx < 1 Or idx 56 Then idx = xlAutomatic to If idx < 1 Or idx 56 Then idx = xlNone Peter T "Peter T" <peter_t@discussions wrote in message ... Try following in the worksheet module (right-click sheet tab view code) Private Sub Worksheet_Change(ByVal Target As Range) Dim idx As Long On Error GoTo errH If Right$(Target(1), 2) = "##" Then idx = Val(Target(1)) If idx < 1 Or idx 56 Then idx = xlAutomatic Application.EnableEvents = False Target(1).Interior.ColorIndex = idx Target(1).ClearContents End If errH: Application.EnableEvents = True End Sub Record a macro while formating cells with your favourite colours to get colorindex's. Type in your colorindex followed by "##". To clear colour format simply type "##". (Obviously you could amend "##") If you want this to work on all sheets, put similar code in the "ThisWorkbook" module but you need to change the event as follows - Private Sub Workbook_SheetChange(ByVal Sh As Object, _ ByVal Target As Range) 'code End Sub Regards, Peter T "haifa" wrote in message ... Does anyone know how I can shade certain cells in by typing in a letter or number, using If statements? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shading Rows With IF Statements? | Excel Worksheet Functions | |||
Sum of all cells with red shading | Excel Discussion (Misc queries) | |||
shading used cells only | Excel Discussion (Misc queries) | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
Shading Cells | Excel Discussion (Misc queries) |