VBA color change then email

[sgi.fanclub]

I all,
I'm wondering if it is possible please.
I require to send emails from Excel when the colour is orange on the last column (Expiry date made with conditional formatting formula)

I want the email to be sent automatically with the Private Sub Workbook_Open() way

Thanks in advance.
Fabien

 

[BobBridges]

I've occasionally written VBA programs that set a cell's background and/or foreground color. I've never tried to get a program to detect those colors, but I gotta believe the capability is there.

The only problem I can see is the definition of "orange". Colors in Excel are precisely defined as combinations of red, green and blue (hence the abbreviation "RGB"), each one a value between 0 and 255. So quite a few combinations could be described as "orange", but your program would likely be looking for one precise set of numbers. You'd have to be sure that whatever sets that color will use the same values every time. Either that, or you have to teach your program to accept a range of values, which would probably be complicated.

 

[Keetoowah]

Hello everyone,
to detect the exact color of the cell you can use the following code

Sub test()
MsgBox Range("B2").DisplayFormat.Interior.Color '<<==== adapt the range
End Sub

then you can use the value in your macro

Sub sendmail()
If Range("B2").DisplayFormat.Interior.Color = 49407 Then

'run your code here
End If
End Sub