1

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?

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.

Improve this question
7
  • 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, 2017 at 18:28
  • Yes, thats the case. I assign it manually.
    – romanzdk
    May 11, 2017 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, 2017 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.
    – romanzdk
    May 11, 2017 at 18:40
  • excelribbon.tips.net/T010780_Colors_in_an_IF_Function.html, stackoverflow.com/questions/30961158/…, exceltrick.com/how_to/sum-cells-based-on-background-color
    – fixer1234
    May 11, 2017 at 22:04

1 Answer 1

Reset to default
-1

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
2
  • 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, 2017 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, 2017 at 10:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .