Solved

color/flashing cells

Posted on 2014-07-31
15
218 Views
Last Modified: 2014-08-14
Hi all,
Well I have attached a sample file, that need manipulate to accomplish my needs.
I have in sheet1 2 "ranges" :
range1 :A6:L25 (table1)
range2 :N6:Y25 (table2)

I need away (vba accepted), that colors the rows like this:
if in A8:A11 range there is a cell.value that matchs in N8:N11 go and color N8:N11 and 08:y8 relatively.
in sample I have:
N8.value=A8.value + A10.value  result:
O8:Y8 colored same as B8:L8 +B10:L10

also if :
C24.value=P24.value color both green
If not Flash/blink Both
same as C25.value=P25
0
Comment
Question by:drtopserv
  • 8
  • 7
15 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40233470
Is it possible that the colors of the rows will be anything other than those shown here?  That is: "Kall" is currently showing red rows; "Mig" shows pink rows.  Could there be other colors for these same values?  

Also, how many possible values might exist for the cells A8:A11?  I'm wondering if a set of conditional formatting rules could be set up if there are not too many and if the colors are mutually exclusive to the values there.

just as an example, see my modified workbook.  The colors are determined by two conditional formatting rules only; no fill colors exist.  I also used conditional formatting for C24=P24 (green if equal, regardless of value), C25-P25 (green if equal).

IMO, blinking cells are annoying and hard to set in an effective manner (ex., number of blinks, rate of blinking, colors to use).  So instead, I created another set of conditional formatting rules that greatly highlight the C and P values when not equal.  Change one to see what I mean. :-)

Also, did you want a similar coloring method applied for the lower table (rows 18:21)?

Regards,
-Glenn
EE-Sample1.xlsx
0
 

Author Comment

by:drtopserv
ID: 40235093
Thanks Glenn Ray for your reply,but not this what i seeking, the values in A8:A11 and N8:N11 are not pre-defined.
means I can`t know what user could put in theses cells, the point is whatever you enter into these field it should seek/match/search the value in the other side and colored it.
about the color specifically, it not a matter which to choose any color is ok (but must be the same for each vale in both tables)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40235105
I understand that the values in rows A8:A11 and N8:N11 could change.  Please remove the existing values from the example file and note that the fill colors disappear.  Then add "Kall" and/or "Mig" to ANY cell in those ranges..you'll see the row highlight automatically.

Again, if there is a limited set of values that could exist in these ranges - and you can assign a specific color for each values - conditional formatting is very effective.

Otherwise, a VBA routine would definitely be required.

Regards,
-Glenn
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:drtopserv
ID: 40236343
Hi again.
well i have tried your solution , still facing problem using conditional formatting instead of vba.
if i change "kall" value to for example "Dar" it will not seeking the value in the other side and colored it .
the values entered in the range are "Unlimited" and also can`t be defined. it could be ANY value .
it must be away to seek the value in both sides (both ranges) then colors value that matchs in same color for both range.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40236403
Okay, you helped explain the evironment much better in your latest response and that confirms that a VBA solution will be required.  It's definitely possible and I'll try to supply a solution for you this weekend.

I will say that even though VBA can produce "flashing" cells, I'm still not a proponent of them.  Does the conditional formatting on the summary cells in rows 24 aznd 25 work for you as in my example?

-Glenn
0
 

Author Comment

by:drtopserv
ID: 40236407
Yup seems ok in rows 24 and 25, but for learning issue If by the way you could also provide code for it  , it will be ok also :}
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40236413
Okay, I'll include a limited flashing option.
0
 

Author Comment

by:drtopserv
ID: 40236414
thnx alot, hope you could provide solution as soon as possible.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40236576
I know you want flashing...and I'll get to that later... but I did come up with a better conditional formatting system that meets your requirements.  Additionallly, you could re-define the color set that I've assigned much easier than if it was in VBA code.

Basically, if a value is in both column A or N, the rows in B:L will highlight as
Red
Orange
Yellow
Green
and the matching value's rows in O:Y will highlight as the exact same color.

Please see the example file to test.  I've also modified the colors on the bottom section (row 24 and 25) to better differentiate them from the colors on top.

If I was writing a VBA routine, it would follow similar logic seen in the conditional formatting rules.

Regards,
-Glenn
EE-Sample1.xlsx
0
 

Author Comment

by:drtopserv
ID: 40236906
needs fix, if in A8:A11 i put 2 same value "go" + "go" , it will not color them.
it`s only  color the left side (N8:N11)
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40237141
Sorry; it works for me.  I inserted "go" in cells A9 and A10 and also in cell N11.  It highlighted the matching rows as you requested:
example match with "go" as test value
Please attach an example of your test file (my latest submission with your values).

Regards,
-Glenn
0
 

Author Comment

by:drtopserv
ID: 40237420
it`s not with same color, same cells value should have same colors.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40247446
Okay, here's another attempt without using VBA...I'd really like to avoid that if possible, even though a Worksheet_Change event would capture and check for value matches.

Here I've added a set of array functions to contain the list of all possible values in A8:A11 and then use conditional formatting to match against that list.  The formula in M8 is
=IFERROR(INDEX($A$8:$A$11,MATCH(0,COUNTIF($M$7:M7,$A$8:$A$11),0)),"-")
and is copied down.  M7 equals 0 and needs to remain so.  I've turned the font white so they aren't seen.

Sorry for the delay.  I hope this meets your needs.
-Glenn
EE-Sample1b.xlsx
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40262252
Hi,

I believe I was able to provide an acceptible solution in my previous post.  If you still have issues with it, please let me know and I'll do my best to resolve them.  Otherwise, please click "Accept this solution" above that post so this thread may be properly closed.

With Thanks,
Glenn
0
 

Author Closing Comment

by:drtopserv
ID: 40262324
after a check, it works!!! , thanks alot glenn ray!! I Appreciate you hard work to supply the connect solution:}}}}
I`ll be glad if you supply also a  vba code just for learning purpose (in case you have time for it)
at now this do the job.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question