Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

color/flashing cells

Posted on 2014-07-31
15
Medium Priority
?
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

661 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