Conditional format of cell range using Formula is condition

[Austinma]

Good evening Ninjas,


Hopefully there should be a simple answer to this:


I have a collection of cells (A1:E16) containing single letter values corresponding to different amino acids (A, T, M, G, K etc). I want to conditional format the 21 different characters according to two different characteristics A or B (Big or Small). Specifically I want to colour the cells in two colours depending on the two characteristics


Something along the lines of:


Colour all cells red if cell contains an A, T, M or G

Colour all cells green if cell contains an K, C, H or L


I’ve had a go myself using the Formula Is condition but all to no avail.


Apologies for my novice-like ramblings,


I look forward to your ideas and help,


Regards,


Mark

 

[Hui]

Mark

Select the range A1:E16


You can use a formula like

=Match(A1,$Y$1:$Y$4,0)>0

assign a Red Fill

Y1:Y4 will contain the letters A, T, M, G


Then assign a second CF

[code]=Match(A1,$Z$1:$Z$4,0)>0

assign a Green Fill

Z1:Z4 will contain the letters K, C, H, L


You can use a Named Formula such as Big: where Big will have the Named Formula [code]={"A","T","M","G"}

Then use a CF formula: =MATCH(A1,Big,0)>0[/code]


Unfortunately in CF's you cannot use array constants like: =Match(A1,{"A","T","M","G"},0)>0[/code]

 

[Austinma]

Good morning Hui,


Apologies for not replying sooner, but I posted the question and then took myself off to catch up on some [much needed] beauty sleep!


I like the idea of using the feeder cell and MATCH combinations that you described above; it will certainly be quicker than how I'm doing things currently.


I've been using conditional find and replace for all 21 characters. Essentially this means that I have to repeat the FIND "X" cell and replace all with formatted "X" for all the 21 amino acid codes in turn. This works well and gets the job done, but is a little labour intensive (but still quicker than going through each cell inturn with a format paintbrush!).


Thanks once again for your help and advice - it's very much appreciated.


Best wishes,


Mark

 

[Austinma]

Hi Hui,


Thanks again for your help in this.


The formulas you suggest work great. To satisfy my curiosity what does the '>0' part of the formula do/define.


Thanks


Mark

 

[SirJB7]

Hi, Austinma!

CF formulas should retrieve a True/False value. Match retrieves the number of the occurrence. So the ">0" is necessary for the boolean value.

Regards!

 

[Hui]

In addition to what SirJB7 said


If =Match(A1,$Y$1:$Y$4,0) doesn't find a value the value will be zero

adding the >0 forces the formula to then Display True/False as required by the CF

 

[Austinma]

Thanks Hui & SirJB7.


Regards,


Mark