Return double amount in case cell is colored (w/o VBA)

[Mandy_84]

Hi all! is that possible to use formula and determine the color at the same time without VBA? For example, Column B has following formula "=IF(MONTH(A1)+1=MONTH(TODAY()),"50","")", but let's say that row 2 and 3 is yellow, so I need the formula to show double of 50 (100), but still based on the formula that catches only last month entries.


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/3/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]8/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR="class: outer_border"]
[TD="width: 126, align: right"]7/31/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[Rick Rothstein]

Hi Rick.
Any idea why it doesn't show 100 for yellow background cell? It gives 50 for all (((

What color yellow did you use to color the cells? The easiest way to tell me what I need to know is to select any one of your yellow cells and then open the VB Editor (press ALT+F11 from any worksheet), locate the window with the caption "Immediate" (if you don't see it, press CTRL+G to make it appear), put this line of code into the "Immediate" window....

Print ActiveCell.Interior.ColorIndex, ActiveCell.Interior.Color

and, with the text cursor anywhere on that line of code, press the Enter Key. Post back what it prints out.

 

[Mandy_84]

What color yellow did you use to color the cells? The easiest way to tell me what I need to know is to select any one of your yellow cells and then open the VB Editor (press ALT+F11 from any worksheet), locate the window with the caption "Immediate" (if you don't see it, press CTRL+G to make it appear), put this line of code into the "Immediate" window....

Print ActiveCell.Interior.ColorIndex, ActiveCell.Interior.Color

and, with the text cursor anywhere on that line of code, press the Enter Key. Post back what it prints out.

6 65535

 

[Fazza]

Late to the conversation, but assuming everything else is OK, see if a full re-calc fixes the issue. IIRC the GET.CELL isn't volatile.

 

[Rick Rothstein]

6 65535

Those are the colors values I used... and what I laid out in Messages 5 and 7 worked for me. And, just to be sure, I tried it again and it still works for me. So, I am not sure what to tell you since I cannot duplicate what you are seeing. All I can says is if you followed the directions as to where to put each of the formulas I posted, it should have worked for you.

Late to the conversation, but assuming everything else is OK, see if a full re-calc fixes the issue. IIRC the GET.CELL isn't volatile.

The formula in the cell in Column B will update if the data in Column A is changed; however, if you change the cell's color, that will not trigger an event that Excel can recognize... so for a color change, yes, you will have to perform a manual recalculation of the sheet to get it to update the values in Column B.

 

[Mandy_84]

Those are the colors values I used... and what I laid out in Messages 5 and 7 worked for me. And, just to be sure, I tried it again and it still works for me. So, I am not sure what to tell you since I cannot duplicate what you are seeing. All I can says is if you followed the directions as to where to put each of the formulas I posted, it should have worked for you.




The formula in the cell in Column B will update if the data in Column A is changed; however, if you change the cell's color, that will not trigger an event that Excel can recognize... so for a color change, yes, you will have to perform a manual recalculation of the sheet to get it to update the values in Column B.

Now I know why...the actions above are related o the yellow cells next to the one where the function is placed, where in my original file, the column where I need to have the result is not so close to yellow column. Thanks anyway!

 

[Fazza]

if the recalc is the issue try adding a volatile function to the GET.CELL formula

such as =GET.CELL(whatever)+0*NOW()

 

[Rick Rothstein]

...the column where I need to have the result is not so close to yellow column...

Yellow column? What yellow column? You said in your original message...

For example, Column B has following formula
"=IF(MONTH(A1)+1=MONTH(TODAY()),"50","")",
but let's say that row 2 and 3 is yellow, so I need
the formula to show double of 50 (100), but still
based on the formula that catches only last month entries.

If the (entire row) is yellow, my code should work. Are you now saying that row is not yellow, only one column is? If so, which column is it?

 

[Mandy_84]

Yellow column? What yellow column? You said in your original message...

If the (entire row) is yellow, my code should work. Are you now saying that row is not yellow, only one column is? If so, which column is it?

Right, I meant cell, sorry

 

[Rick Rothstein]

Right, I meant cell, sorry

I am going to sleep now, but I will need to know what column has the yellow color in it when I get up?

 

[Mandy_84]

I am going to sleep now, but I will need to know what column has the yellow color in it when I get up?

Hope you had a good sleep ))))
So, let’s say we have 4 columns(A,B,C,D), column A has few “yellow” cells with some figures and I expect that values of the yellow cells in column A will return doubled amount (100) into column C based on the date as stated above, thus uncolored ones will give me a normal (50) values. Hope I made it clear. Anyway, what u gave me earlier works perfect in case this yellow cell is close to the outcome cell.