nbert


I have an excel sheet. In this sheet i check if a cell is equal to a certain formula. if thats the case i say it has te be yellow.(thats commandbutton4_click)
NOw that works fine.
But after that i made a new button(commandButton5) that has to make the sum of all the yellow cells. ANd that one doenst work. Cause he cant see the yellow of the format condition. For the program its still white. How can i solve this problem By the way the button fully does his job if i manually put the needed cells in yellow.
Private Sub CommandButton4_Click()
'
' FormuleTeletravailJaune Macro
' Mettre production teletravail en jaune sur base des criteres - fichier chiffres production - Macro recorded 22/05/2007 by uspvd
'
'
Range("C6").Select
Application.CutCopyMode = False
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B$1EmbarrassedB$4;C31)"
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.Copy
Range("D6:Y6").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("C6:Y6").Select
Selection.Copy
Range("C7:Y29").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub
Private Sub CommandButton5_Click()
'Dim i, j, a, b, totaal As Integer
totaal = 0
For i = 6 To 65535
If Cells(i, 3) = "" Then
j = i
i = 65535
End If
Next i
i = 6
a = 3
For i = 6 To j
For a = 3 To 34
If Cells(i, a).Interior.ColorIndex = 6 Then totaal = totaal + Cells(i, a)
Cells(i, 35) = totaal
Next a
totaal = 0
Next i

Cells(66, 1) = Cells(6, 12).Interior.ColorIndex
End Sub



Re: format conditions in excel

Andy Pope


Hi,

The conditionally applied formatting is not directly available.
You either need to do the same formula test in code or unravel the conditional format properties.

See here for more information.
http://www.xldynamic.com/source/xld.CFConditions.html