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).

enter image description here

Basically advanced IF function.

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?


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)
    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

Color is not data. If you manually color a cell then the logic for doing so is in your head, where Excel cannot access it. Put the logic for the color into the spreadsheet. Then you can use the logic for coloring and also for aggregating the data with formulas.

| improve this answer | |
  • 1
    While I fully agree with your opinion I don't think this is an answer, it's more a comment to the question. (an answer would either solve the problem, either state it's impossible. Stating "you've a wrong approach" is neither of them) – Máté Juhász May 12 '17 at 8:08
  • @MátéJuhász My suggestion is in my answer. "Put the logic for the color into the spreadsheet ..." Unfortunately, the screenshots with arbitrarily colored cells do not reveal the reasoning behind coloring the cells. Therefore my suggestion is a bit vague, yes, but it is the first step to a better approach. Conceptually. Details can follow. So many people use cell fill and then expect that they can do something based on that cell color. I pointed out how to do it differently, independent of the particular case file. If that deserves a down vote in your eyes, so be it. – teylyn May 15 '17 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.