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? |
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? |
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? |
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? |
All times are GMT +1. The time now is 07:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com