VBA message box in excel

[coltaalex]

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

 

[SkipVought]

Your conditional formatting uses a formula.

What is the formula or condition that CFs the cell red?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[coltaalex]

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]

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).SetFirstPriority
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).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1

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

 

[SkipVought]

All you have to do, no VBA required is in any cell...
[tt]
=SUMPRODUCT(--(Rates)*(Your Column P - same rows as Rates))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

[tt]
=SUMPRODUCT(--(Rates)*(Rates=Your Column P - same rows as Rates))[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[coltaalex]

you mean i have to use this formula in Conditional Formatting,

because my column already has formula, based on the content an color my cells
and how about message box,

 

[SkipVought]

IN A CELL, not in CF!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The other thing you could do is put your CF formula in the sheet in each row in a new column and then SUM that column.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[coltaalex]

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

 

[SkipVought]

The fact is that you can use the CF logic to arrive at your answer on the sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[coltaalex]

Good Morning Skip,
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