extend conditional format

[jmwismer]

Hi There,
I have seen some posts on sophisticated extension of conditional formatting but those only took care of colors. what I would like to have is:

if cond1
-specify color
-specify bold or not
-specify italic or not
-specify cell color
-specify cell (not content) underline

if cond2, etc..

anyone?

thanks a lot,
jm

[VBA Noob]

Something like

Uses the select case. If you enter one of the two conditions in Col A it changes cell font and interior colours etc

Code goes in the worksheet

http://www.contextures.com/xlvba01.html#Worksheet

http://www.ozgrid.com/VBA/select-case.htm

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Select Case Target.Value
    Case 100
Target.Interior.ColorIndex = 5
With Target.Font
    .Bold = True
    .Italic = True
    .Color = 17
    .Underline = True
End With
   Case 200
Target.Interior.ColorIndex = 15
With Target.Font
    .Bold = False
    .Italic = False
    .Color = 27
    .Underline = False
End With
End Select
End If
End Sub

VBA Noob

[jmwismer]

Thank you VBA Noob,

sorry for those neewbie VBA add'l questions::

- how is th sub called? do I need to run a macro? can this be applied automatically?
- How if I want to do the CASE on column A, and apply changes on columns B to D?
- what is the code to underline the cell border (vs. content)?

thx again,
jm

[VBA Noob]

- how is th sub called? do I need to run a macro? can this be applied automatically?

Read link. Explained there

- How if I want to do the CASE on column A, and apply changes on columns B to D?

Change "A:A" to "A:C" to work with Column A to C

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

- what is the code to underline the cell border (vs. content)?

With Target.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

VBA Noob

[jmwismer]

cool, getting there. Works great on the cell I ENTER a value on.

I would need however :

1: depending on what cell in Col A is, will change format in Cols B-D

2: should do this based on e.g. A1 which is a formula, i.e. I am not actually changing or entering anyting on A1. But depending on many other cells, A1 will have a value which I use to format B1 C1 and D1.

thx,
jm

PS: what is the code to remove the border?

[VBA Noob]

Remove borders

Target.Borders.LineStyle = xlNone

Use offset to change Col B formatting

e.g

Target.Offset(0, 1)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
Select Case Target.Value
    Case 100
Target.Offset(0, 1).Interior.ColorIndex = 5
With Target.Offset(0, 1).Font
    .Bold = True
    .Italic = True
    .Color = 17
    .Underline = True
End With
   Case 200
Target.Offset(0, 1).Interior.ColorIndex = 15
With Target.Offset(0, 1).Font
    .Bold = False
    .Italic = False
    .Color = 27
    .Underline = False
With Target.Offset(0, 1).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
End With
End Select
End If
End Sub

VBA Noob

[jmwismer]

Noob,

- Offset(0,n) takes care of one offset cell. how about doing it on several columns? (will change C AND D)?

- There is still a major difference with the regular "conditional formatting". The sheet I work on is purely a sheet of formulas based on other sheets. I am not typing anything into this sheet, but I need it to reformat itself. The code seems not to be able to do so. Maybe _change is not the right trigger? _Calculate maybe?

thx,
jm