PDA

View Full Version : Mesage box in VBA



coltaalex
07-02-2010, 12:25 PM
Hi, could someone help me with VB, i have a column,
some cells in the column are red some are white after ruing a macro, some times no red at all - (conditional formatting ),
So i want a message box to appear if i have any red cells,
thank you

Bob Phillips
07-02-2010, 02:49 PM
It is not easy to test for conditionally formatted cells, far better to test for the same criteria that sets it red. What is the condition?

coltaalex
07-02-2010, 03:21 PM
actually i color the whole column in red, the with conditional formation i make white cell, and the rest of them remain red, so are the red cells a want a message box,
i use from red to white :

= countif(rates,P11)

and for making the white the empty (which are red) cells :

=LEN(TRIM(M11))=0

coltaalex
07-02-2010, 03:21 PM
and here is my recorded macro :

Sub Rates()
'
' Rates Macro
'

'
Application.Goto Reference:="R11C11:R10000C11"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255

End With
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(Rates,K11)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1

End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(K11))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1

End With
Selection.FormatConditions(1).StopIfTrue = False
Range("K11").Select
End Sub

Bob Phillips
07-02-2010, 03:50 PM
That made absolutely no sense to me, and I am not clear what the CF is, the COUNTIF seems to do nothing, and the other just tests for data.

coltaalex
07-02-2010, 03:59 PM
i think i didn't explain correctly

look i have two columns, with numbers, i the second column i introduce values sometime, and those values usually are the same like in first column, if the value is not in the first column i want that cell to be colored in red ( in the second column)
i have to check the second columns (to see if i have those values in the first column) and if i don't have the number in the fist column, then i want the the cell red in the second column,

i cannot introduce formula in the excel ( in cell A) because i have values in that cell,
this should be dome in conditional formatting or in the VBR

coltaalex
07-06-2010, 06:42 AM
Good Morning
the data in cell is changing all the time, and they are random, they are introduced by hand in the cell, so in this case the formula will be deleted , i cannot not introduce it all the, time, because this guys who will work with this sheet, they have no idea about formulas