To get right color

Question

Hello guys
My question is about to get the right color. I have 2 columns named Shelf and Tray and values inside those columns. So depends on sum of values in columns I want to have different colors in next column(say column Status). It look like this:

Shelf Tray Status
1 1
1 1
1 1
1 2
1 2
1 3
So if I have Shelf and Tray with values 1 (3 times) I want to have red color in Status column. If I have Shelf and Tray with values 1 and 2 (2 times I want to have yellow color in Status column) and If I have Shelf and Tray with values 1 and 3(only 1 time, I want to have green color in Status column). This means 3 times with same values don't have free space for more, 2 times means it is 1 free space and 1 times means 2 free space.
If someone knows how to fix this in Vba excel or C#, maybe with If else statement or loop.
Thanks in advance.

Answer 1

If it is Excel sheet, you can use Excel worksheet function SUM or IF

Answer 2

Or Countif

Answer 3

Yes it is. Do u have some similar code?

Answer 4

Yes, I Have, but at work.

Answer 5

It is opposite Function - after user set background colors of Cell, on other sheet calculates (in row Are employees And column Are days)

Answer 6

It is If else statement with Cell.Interiror. ColorIndex
I know all those functions because I got programming Asp Net, C#
But in excel I didn't code before. So it is not same(100%)
But it is similar

Answer 7

I also need for jobb. Can u send me tomorrow😄

Answer 8

Hope I do not forgot

Answer 9

Hahha don't do it😄
I fixed on other way. I added a new column(extra column) and sum it. Then wrote code and it works. But I want to fix without extra column if it's possible

Answer 10

And what Is in your code? If...else construction? VBA has Select Case ...End Select

Answer 11

Select Case Is like switch

Answer 12

Cell.Interior.ColorIndex you need know which index Is for red And So on. Try record Macro And set background color any Cell, then stop recording you Will see in VBA (Alt+F11 in Excel)

Answer 13

Then in if...else...statement set condition like 1+3

Answer 14

You Have Space after Interior. May be it is problem

Answer 15

No, it's not problem. It works like that but like I sad it's with extra column. I want to fix it work with only Shelf nad Tray columns.
But don't know how to do it🤣🤣

Answer 16

If ActiveCell.Offset(0,-2).Value + ActiveCell.Offset(0,-1).Value == 3 Then ActiveCell.Interior.ColorIndex=red

Answer 17

Similar for 2 And 1

Answer 18

What it means with those Offset(0, -2) and Offset (0,-1)
Did u tried this? Works?

Answer 19

ActiveCell Is Cell where you Have selected. For example C1. Then ActiveCell.Offset(0,-2) Is A1. Offset(row,column)

Answer 20

https://www.excel-easy.com/vba.html