vba .interior

[Noz2k]

Is there a way to recall the .interior properties of a cell?

I currently have code like

 Private Sub Worksheet_Change(ByVal Target As Range)
With Range("C5:C55").Interior
Select Case Range("C56").Value
Case 0
.ColorIndex = 9
.Pattern = xlSolid
.TintAndShade = 0
Case 1 To 2
.ColorIndex = 9
.Pattern = xlSolid
.TintAndShade = 0.1
Case 3 To 4
.ColorIndex = 9
.Pattern = xlSolid
.TintAndShade = 0.2
 
etc

but this isn't set quite how I want it. So I would like to use conditional formatting on some cells in excel and then basically copy the cell properties from that to use for my code.

 

[RoryA]

Conditional formatting does not affect the Interior properties unfortunately. If you have 2010, then you can access the displayed conditions directly; if not, then you need to evaluate all the format conditions, which can get very complicated on 2007 (it's still convoluted on 2003 and earlier, but there aren't as many options to cater for).

 

[Noz2k]

Thanks,

I do have 2010, how do I access these? and how do I set them in vba?

 

[RoryA]

You set conditional formatting using the FormatConditions collection, just as in previous versions. The Range object now has a DisplayFormat property that you can use to access the currently displayed properties of the cell:

Activecell.displayformat.interior.Themecolor

for example.

 

[Noz2k]

I take it I use .FormatConditions in VBA.

However I'm unsure how I set the range to average?

I would like C5:BB55 to be the cells formatting, based on the corresponding column values in the row C56:BB56.

So looking at it I'm thinking I need to use .ColourScale

but unsure how this works in VBA

 

[RoryA]

I have no idea what you are trying to do from that - can you be more specific please?
You should be able to apply the CF manually while recording a macro to get an idea of the code required.

 

[Noz2k]

Ok, so I have recorded a macro which gives me an idea

Sub ConditionalFormatting()
    Range("C56:BB56").Select
    Selection.FormatConditions.AddColorScale ColorScaleType:=2
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
        xlConditionValueLowestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 16776444
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
        xlConditionValueHighestValue
    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
End Sub

however I want this formatting to be applied to range("C5:BB55") but based on the values in the range ("C56:BB56")

 

[RoryA]

Then you'll need to use a formula rather than cell values and record doing that.

 

[Noz2k]

I can't do it using a formula in excel, which is why I'm trying to do it in vba.

How about if I format the range(C56:BB56) and then copy the format to the cells in the rows above? Is it possible to do this?

 

[RoryA]

I mean that you will have to use a formula in the CF dialog. Otherwise you cannot format one set of cells based on the values of another.