Conditional formatting of 3 columns according to 3 discrete cells

[azzaman77]

Hello.

A little background on what I am doing. I have a spreadsheet that helps me determin the number of cable glands according to the type of cable.
That means, I have cable list that spreads over 4 columns, 3 of which are relevant for my problem, and I need to count how many cables of each type I have, to accomplish what I am tryin to.

File is attached.

Long story short, I'd like to CF colour cells in B, C & D columns, same as they look in columns F, G & H (this is my auxiliary table, manualy made, that is supposed to be "upgraded" occasionaly).


An example, cells B2:D2 should be same colour as F30:H30.

I've tried CF > new rule > format only cells that contain > specific text > containing > =(F$30:H$30) (You can see this in CF > manage rules)

This specific "lame/newbie" solution works with cells B2:D2, but it also colours and other cells in these 3 columns containing any value of the (F$30:H$30), that means, useless.



Later on, I'd like to count all of the coloured cells respectively in, lets say B column to get numbers I need (would be used in K7:K11).


All this is supposed to help save time, as when I copy data from my other programe, I get this first 4 columns and it would be nice to instantly see numbers I need (R7:R11).

[MarvinP]

Hi and welcome to the forum,

I've used some VBA instead of Conditional Formatting to produce what I think you want. The attached has the following features.

1. A Dynamic Named Range (DNR) that will span your data from A1 to D89. If you add more rows this dynamic named range, which I've called "DataTable" will expand.
2. An event macro behind your PCB6 sheet that will fire if you change anything in the DataTable range
3. VBA code that will change the colors of all the rows depending on what colors you have in Column F of your lookup table.

I hope this is what you were looking for.

[hemesh]

Welcome you can also use Below formula
go to CF New rule --> determine a formula --> copy paste =ISNUMBER(MATCH($B2&$C2&$D2,$F$7:$F$12&$G$7:$G$12&$H$7:$H$12,0)) and format as yellow applies to =$B$2:$D$89

Again new rule =ISNUMBER(MATCH($B2&$C2&$D2,$F$13:$F$24&$G$13:$G$24&$H$13:$H$24,0)) format as green applies to same as above

Again new rule =ISNUMBER(MATCH($B2&$C2&$D2,$F$25:$F$28&$G$25:$G$28&$H$25:$H$28,0)) format as blue and applies as above

Again new rule =ISNUMBER(MATCH($B2&$C2&$D2,$F$29:$F$30&$G$29:$G$30&$H$29:$H$30,0)) format as orange applies as above

[hemesh]

Find the attached file. array formula is used to count the colour of cells ( to enter array formula instead of pressing control and enter, Hold control and shift then hit enter which is confirmation of array. You will find curly braces {} surrounding your formula, which cannot be entered manually)

[hemesh]

find the attached

[newdoverman]

You will find that you have some cells that have spaces where there shouldn't be any. This will cause rules to fail in those cells. There are also cells near the bottom of your data list that don't have any matching values in the CF criteria listing.

[hemesh]

Yup I just checked you are right sir but this is I dont know showing as char 32 but I am not able to clear it

[newdoverman]

I cleaned up the data on your worksheet by using the TRIM function in an added column then copying and pasting values back to the original column. I repeated this for all the data and Conditional Formatting columns and then the conditional formatting performed well.

[azzaman77]

Thank You all for quick respond and your help, i really appriciate it.

Yes I realise that problem with unnecessary "spaces" in text, thanks for explaining newdoverman, but this is some other issue, consernig input data.

Thank you MarvinP for your VBA, but it reports bug after bug when I delete this first 4 columns, as they are suposed to be "blank" for paste-ing data.

Hemesh thank you aswell, especially because your solution seems more "undestandable" for me, I will take some time to explore this .

Again, thank you all for help, but I will need some time to try to understand it by myself, because this is purpose of your help - not to do everything instead of me .


I will respond later, maybe with some additional questions.


Cheers!