0

To get right color

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.

11th Apr 2021, 3:45 PM
Alvedin
Alvedin - avatar
25 Answers
+ 1
If it is Excel sheet, you can use Excel worksheet function SUM or IF
12th Apr 2021, 7:43 PM
Václav Dostál
Václav Dostál - avatar
+ 1
Or Countif
12th Apr 2021, 7:44 PM
Václav Dostál
Václav Dostál - avatar
+ 1
Yes it is. Do u have some similar code?
12th Apr 2021, 7:46 PM
Alvedin
Alvedin - avatar
+ 1
Yes, I Have, but at work.
12th Apr 2021, 7:48 PM
Václav Dostál
Václav Dostál - avatar
+ 1
It is opposite Function - after user set background colors of Cell, on other sheet calculates (in row Are employees And column Are days)
12th Apr 2021, 7:52 PM
Václav Dostál
Václav Dostál - avatar
+ 1
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
12th Apr 2021, 8:13 PM
Alvedin
Alvedin - avatar
0
I also need for jobb. Can u send me tomorrow😄
12th Apr 2021, 8:01 PM
Alvedin
Alvedin - avatar
0
Hope I do not forgot
12th Apr 2021, 8:02 PM
Václav Dostál
Václav Dostál - avatar
0
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
12th Apr 2021, 8:06 PM
Alvedin
Alvedin - avatar
0
And what Is in your code? If...else construction? VBA has Select Case ...End Select
12th Apr 2021, 8:10 PM
Václav Dostál
Václav Dostál - avatar
0
Select Case Is like switch
12th Apr 2021, 8:11 PM
Václav Dostál
Václav Dostál - avatar
0
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)
12th Apr 2021, 8:20 PM
Václav Dostál
Václav Dostál - avatar
0
Then in if...else...statement set condition like 1+3
12th Apr 2021, 8:22 PM
Václav Dostál
Václav Dostál - avatar
0
You Have Space after Interior. May be it is problem
12th Apr 2021, 8:24 PM
Václav Dostál
Václav Dostál - avatar
0
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🤣🤣
12th Apr 2021, 8:26 PM
Alvedin
Alvedin - avatar
0
If ActiveCell.Offset(0,-2).Value + ActiveCell.Offset(0,-1).Value == 3 Then ActiveCell.Interior.ColorIndex=red
12th Apr 2021, 8:34 PM
Václav Dostál
Václav Dostál - avatar
0
Similar for 2 And 1
12th Apr 2021, 8:35 PM
Václav Dostál
Václav Dostál - avatar
0
What it means with those Offset(0, -2) and Offset (0,-1) Did u tried this? Works?
12th Apr 2021, 8:38 PM
Alvedin
Alvedin - avatar
0
ActiveCell Is Cell where you Have selected. For example C1. Then ActiveCell.Offset(0,-2) Is A1. Offset(row,column)
13th Apr 2021, 4:43 PM
Václav Dostál
Václav Dostál - avatar
13th Apr 2021, 5:58 PM
Václav Dostál
Václav Dostál - avatar