Colors in an IF Function

[Mal4131]

Hi All- I am attempting to return a specific value if the text color is red. I have the following macro, which is functioning, but only for a single cell:

Function TT(r As range) As String
If r.Font.Color = RGB(255, 0, 0) Then
TT = "Complete- Requires IL Address Update"
ElseIf r.Font.Color = RGB(0, 0, 0) Then
TT = "Complete"
Else
TT = "Pending"
End If
End Function

However, I need it to return a specific cell based on a vlookup. Is it that I change the range in the macro, or am I missing something in the formula? (Let column 19= Red value, Black value, or blank cell and Let A4=unique identifier/NPI). My macro skills are very basic and I'm not certain of the logic for combining it into a formula. I appreciate any assistance here. Thanks very much.

=IF(VLOOKUP(A4,'New MSO'!1:1048576,19,FALSE)="","",IF(tt(VLOOKUP(A4,'New MSO'!1:1048576,19,FALSE)="Complete- Requires IL Address Update"),"Complete- Requires IL Address Update","Complete"))

 

[James006]

Hi,

Not sure to understand your problem ...

May be ...

=IF(VLOOKUP(A4,'New MSO'!1:1048576,19,FALSE)="","",TT(A4))

Hope this will help

 

[Mal4131]

=IF(VLOOKUP(A4,'New MSO'!1:1048576,19,FALSE)="","",TT(A4))


Hi James- Thanks for your reply. The above doesn't quite get what I'm looking for. I receive a weekly report with the below sample data. I need to generate a status for our master spreadsheet to track the provider's license application. Thanks again for your help here.

For each provider:
If the text in column 5 is red, return the status --> "Complete- Requires IL Address Update"
If the text in column 5 is black, return the status--> "Complete"
If column 5 is blank, return the status--> "Pending"

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]NPI[/TD]
[TD]Name[/TD]
[TD]License #[/TD]
[TD]Controlled Substance#[/TD]
[TD]DEA#[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]John Smith, MD[/TD]
[TD]456789[/TD]
[TD]335195[/TD]
[TD]AB3256[/TD]
[/TR]
[TR]
[TD]54698[/TD]
[TD]Jane Smith, MD[/TD]
[TD]325684[/TD]
[TD]566658[/TD]
[TD]CD8859[/TD]
[/TR]
[TR]
[TD]85922[/TD]
[TD]Mary Smith, MD[/TD]
[TD]445896[/TD]
[TD]555289[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

Based on your example ... say your data is located in Range A1 to E4 ...

What happens if in cell F2 ... you type the formula : =TT(E2)

Then you can copy this formula down in your Column F

To be on the safe side ...
Below a tiny improvement to your UDF ...

Function TT(r As Range) As String
Application.Volatile
If IsEmpty(r) Then TT = "Pending": Exit Function
  If r.Font.Color = RGB(255, 0, 0) Then
    TT = "Complete- Requires IL Address Update"
  ElseIf r.Font.Color = RGB(0, 0, 0) Then
    TT = "Complete"
  End If
End Function

Hope this will help

 

[FormR]

Hi, another option for you to try.

=IF(VLOOKUP(A4,'New MSO'!$A:$S,19,FALSE)="","Pending",tt(INDEX('New MSO'!$S:$S,MATCH(A4,'New MSO'!$A:$A,0))))

 

[Mal4131]

Thank you FormR! This works great. I appreciate you both taking the time to help. Happy Holidays!