four cells, constitute one, two, three or four cells, depending on value

[ThomasBP]

Hi
Happy New Year to everyone.
I have a question and don't know if it can be done, that's why I try keeping it simple!

I have a sheet1, on this I have four cells:
2A, 2B, 2C and 2D, these four cells have a total width of 40 pixels.

Then I have a sheet2 where I also have four cells again:
2A, 2B, 2C and 2D, these cells may content a character "x".

- For example,if on sheet 2 there is an "X" in either 2A, 2B, 2C or 2C, then I want the 4 cells on sheet1 to be merged into one 40pixel cell and given the background color "green".
- On the other hand, if there is 2 "X" on my sheet2, in a combination of 2A, 2B, 2C or 2C, then I want the 4 cells on sheet1 to be merged into two cells (ie 2A + 2B and 2C + 2D) so they both become 20px wide and have a total width of 40pixel and get background color "green" and "blue" respectively.
- And if I for example in sheet2 have three "X" in 2A, 2B, 2C or 2D (ie 3 out of the 4 possible) then I want the 4 cells on sheet1 to be merged so that 2B and 2C are merged and that 2A, 2B + 2C, as well as 2D, have a width of 13 pixels, so they have a total width of 39pixels and get background colors "green", "blue" and "red" respectively.
- And lastly, if there is an "X" in all of the four cells (2A, 2B, 2C and 2D) then no cells should be changed and stained with "green", "blue", "red" and "yellow" color, each.

Hope it has not become too complicated and that it can be done, I can fine-tune the cells, but do not know exactly how to access the function, so based on the number of "X" is on my sheet2, can merge the cells on sheet1 to 1 or 2 and give these colors, or merge 2B and 2C, where the "three" cells on sheet1 then become of equal width and become filled with 3 colors.

about the four "X" is, then I think I can, because there is no change of the cells on sheet1, it is a question that if 2A on sheet2 has an "X" then 2A on sheet1 have the color "Green".

Can some guide / help me on the right path :-)

In advance thank you!

[BMV]

ThomasBP, there is no worksheet function that can merge cell. There could be trick with UDF but I think you need format range of cells but small example can explain more.

Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results.

[ThomasBP]

Hi BMV

I have been looking around and can see that I can handle it with some VBA, I have tried to make a VBA so that if three out of my four cells on sheet2 (A2, B2, C2 and D2) have an "X", then I want my sub "4Cells2_3" function to be activated and if there are 2 of the fields that have an "X" then it is my sub "4Cells2_2" function that gets activated on my sheet1.

My SUB function is the following, my question is just how can I activate my function?

Please Login or Register  to view this content.

[AliGW]

This thread is now in the VBA section.