[SOLVED] Skyline with Colors


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

New Reply
 
Thread Tools Rate Thread Display Modes
  #1  
Old 26th April 2014, 01:52
azad padamsey's Avatar
azad padamsey azad padamsey is offline
Junior Member
MS Office Version: Excel 2010
MS Office Skill Level: Proficient with Excel
 
Join Date: May 2010
Location: mwanza
Age: 65
Posts: 78
Posts Per Day: 0.03
My Top Tip Count: 0
Thanks to others: 2
Thanked 0 Times in 0 Posts
Reputation: 10 (8) azad padamsey is on a distinguished road
Australia
Default Skyline with Colors
     Excel: Functions   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: Functions   

Hi,
I am now inserting a row below each cell and applying a color if a value above it exists.
Please see attached file.
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.

The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #2  
Old 26th April 2014, 07:27
RoyUK's Avatar
RoyUK RoyUK is offline
Administrator
Solution Centre Professional
MS Office Version: MS Office 2003, 2007, 2010
MS Office Skill Level: Proficient with MS Office
 
Join Date: Nov 2008
Location: Derbyshire, UK
Posts: 2,707
Posts Per Day: 0.83
My Top Tip Count: 4
Thanks to others: 2
Thanked 195 Times in 193 Posts
Reputation: 3320 (69) RoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond reputeRoyUK has a reputation beyond repute
4 Top Tips
England
Default Re: Skyline with Colors

------ Register to get rid of these "In Post" ads! ------


So what is the question?
__________________
Hope that helps, RoyUK
For tips & examples visit free excel help
DatabaseForm
About me

  #3  
Old 28th April 2014, 00:45
azad padamsey's Avatar
azad padamsey azad padamsey is offline
Junior Member
MS Office Version: Excel 2010
MS Office Skill Level: Proficient with Excel
 
Join Date: May 2010
Location: mwanza
Age: 65
Posts: 78
Posts Per Day: 0.03
My Top Tip Count: 0
Thanks to others: 2
Thanked 0 Times in 0 Posts
Reputation: 10 (8) azad padamsey is on a distinguished road
Australia
Default Re: Skyline with Colors

------ Register to get rid of these "In Post" ads! ------


Hi,
I would like to Color a cell below if a value exists above it.
At present the totals don't match.Please see the file I sent.
Regards
Azad

The Code Cage Advertisment
Advertisement
  #4  
Old 28th April 2014, 15:19
NBVC's Avatar
NBVC NBVC is offline
The Code Cage Staff
MS Office Version: Excel 2003 - 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2008
Location: Mississauga Canada
Posts: 1,364
Posts Per Day: 0.42
My Top Tip Count: 2
Thanks to others: 64
Thanked 81 Times in 81 Posts
Reputation: 1435 (36) NBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud of
2 Top Tips
Canada
Default Re: Skyline with Colors

------ Register to get rid of these "In Post" ads! ------


Are you talking about the cells you currently have green? There already seems to be a conditional format applied.

If you want to change the logic of the formatting to checking above cell then try this:

Select range from K476 to GR854 (bottom right corner of data).

then go to conditional formatting and select "use a formula to determine which cells to format"

then enter formula:
My Formula:
=Search("-",K475)


or
My Formula:
=K475<>""


Then click Format and choose color.
__________________
Where there is a will there are many ways. You just have to pick one!
Microsoft MVP - Excel

  #5  
Old 29th April 2014, 00:14
azad padamsey's Avatar
azad padamsey azad padamsey is offline
Junior Member
MS Office Version: Excel 2010
MS Office Skill Level: Proficient with Excel
 
Join Date: May 2010
Location: mwanza
Age: 65
Posts: 78
Posts Per Day: 0.03
My Top Tip Count: 0
Thanks to others: 2
Thanked 0 Times in 0 Posts
Reputation: 10 (8) azad padamsey is on a distinguished road
Australia
Default Re: Skyline with Colors

------ Register to get rid of these "In Post" ads! ------


Hi,
Many thanks.The coloring works OK.However there are 22 values for 5-Jul-14,but only 11 are showing.I don't know the reason.
Azad

  #6  
Old 29th April 2014, 13:32
NBVC's Avatar
NBVC NBVC is offline
The Code Cage Staff
MS Office Version: Excel 2003 - 2013
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2008
Location: Mississauga Canada
Posts: 1,364
Posts Per Day: 0.42
My Top Tip Count: 2
Thanks to others: 64
Thanked 81 Times in 81 Posts
Reputation: 1435 (36) NBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud ofNBVC has much to be proud of
2 Top Tips
Canada
Default Re: Skyline with Colors

------ Register to get rid of these "In Post" ads! ------


oK.

The formula I gave you on another thread was depending on Row number to give count. But you have placed the formula in alternate cells now, so you are only getting half the actual results.

Since you numbered every other row in your table in column J. We can use that instead.

Also, not sure if you need a YES/NO result in the "inbetween" cells or not, but if you simply want them blank, and colour only if a code exists above the respective cells, then we can create a formula that combines the two and so you can copy just that formula up and across.

For example, try this formula in K854:

My Array Formula:
=IF($J854="","",IFERROR(INDEX(Sys,SMALL(IF(Date_We=K$856,ROW(Date_We)-MIN(ROW(Date_We))+1),$J854)),""))

This is an Array formula. Type or paste the formula into the cell(s) as directed, then press F2 to ensure the cell is active. Hold the Ctrl and Shift keys down and then press ENTER.
This is often referred to as committing using CSE. You should see curly { } brackets around the formula. Copy the formula down or across as needed.


Then copy up and across.

Then for conditional formatting apply the second formula I gave you in my previous post:

i.e.

My Formula:
=K475<>""
after selecting range: K476:GR854
__________________
Where there is a will there are many ways. You just have to pick one!
Microsoft MVP - Excel

The Following User Says Thank You to NBVC For This Useful Post:
azad padamsey (30th April 2014)
The Code Cage Advertisment
Advertisement

To stop seeing these ads and get other benefits check This page!

  #7  
Old 30th April 2014, 00:04
azad padamsey's Avatar
azad padamsey azad padamsey is offline
Junior Member
MS Office Version: Excel 2010
MS Office Skill Level: Proficient with Excel
 
Join Date: May 2010
Location: mwanza
Age: 65
Posts: 78
Posts Per Day: 0.03
My Top Tip Count: 0
Thanks to others: 2
Thanked 0 Times in 0 Posts
Reputation: 10 (8) azad padamsey is on a distinguished road
Australia
Default Re: Skyline with Colors

------ Register to get rid of these "In Post" ads! ------


Hi,
Thank you so much for your help.The solution works perfectly.
Once again many thanks.
Azad

  #8  
Old 30th May 2014, 08:10
FutureC's Avatar
FutureC FutureC is offline
Junior Member
MS Office Version: MS Office 2007
MS Office Skill Level: Proficient with MS Office
 
Join Date: May 2014
Location: Walnut
Age: 34
Posts: 6
Posts Per Day: 0.00
My Top Tip Count: 0
Thanks to others: 0
Thanked 0 Times in 0 Posts
Reputation: 10 (4) FutureC is on a distinguished road
Default Re: Skyline with Colors

------ Register to get rid of these "In Post" ads! ------


That's cool suggestion.

The Code Cage Advertisment
Advertisement
New Reply
The Code Cage at Facebookhe Code Cage at Twitter


Tags
colors, skyline

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 11:15.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.19325 seconds
  • Memory Usage 6,582KB
  • Queries Executed 16 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (4)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (8)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (2)navbar_noticebit
  • (41)option
  • (8)post_thanks_box
  • (1)post_thanks_box_bit
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit
  • (8)post_thanks_postbit_info
  • (8)postbit
  • (1)postbit_attachment
  • (8)postbit_ctoptip
  • (8)postbit_onlinestatus
  • (36)postbit_reputation
  • (8)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (2)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • error_fetch
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete