# Excel custom function with IF and cell color

I need help with maybe custom function for excel.

Everything is in the picture: (Yes, I know that COLOR=YELLOW is nonsense, but its just example of what I think it could solve it).

For example:

If source cell contains "R" without background color, then make end cell F7 and without background too. However, If source cell contain "R" with yellow background color, then make end cell F7 and with yellow background color too.

Could you help me with that please?

EDIT:

Im not really good at vba, so I wrote something like this:

This is original basic formula:

``````If (PSANI[@PO2]="R";[@Lokace];"")
``````

And this I wrote for VBA code:

``````Function ExtendedIF (rng As Range) As String

If (PSANI[@PO2]="R") And (PSANI[@PO2].Interior.ColorIndex = -4142) Then
ExtendedIf = [@Lokace]
ElseIf (PSANI[@PO2]="R") And (PSANI[@PO2].Interior.ColorIndex <> -4142 Then
ExtendedIF = [@Lokace] And Interior.ColorIndex = RGB (255, 230, 153)
Else
ExtendedIf = Nothing
End If
End Function
``````

Could you help me with editing this code to be right please? I dont know how to write correctly those relative ranges.

• How is the background color currently being determined? Is there a formula you're using, or something like that? Or is it randomly assigned? I ask because if there's a way to determine if it should be Yellow, you can use Conditional Formatting for everything. If you're manually assigning a color, and have to check that, then you'll need VBA. – BruceWayne May 11 '17 at 18:28
• Yes, thats the case. I assign it manually. – Roman Žydyk May 11 '17 at 18:29
• is there a formula we can use to determine which ones you color? Or maybe could we use a helper column...where if you put `x`, then highlight cells yellow. If no x, no color? – BruceWayne May 11 '17 at 18:36
• unfortunately there is no formula. that data are being refreshed (background color change) every week. manually selected some of them and colored. however the helper column could solve it, but I would be rather if it could be solved without the helper column. but of course if its not possible, I have no other option. – Roman Žydyk May 11 '17 at 18:40