Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 11-13-2019, 03:47 PM
mch375 mch375 is offline
CAS SOA
 
Join Date: Mar 2019
College: Postgraduate
Posts: 16
Default Excel VBA: Coloring cells in column based on the color of other cells in column

I am trying to color cells based on the color of the other cells (standard color light green). After finding out that conditional formatting can't be applied, I wrote the code below but it does not return anything. If there is a better way to write it, I'll be happy to know too. Thank you for your feedback.
Code:
For i = 2 To lastrow
    
    If Range("D2:D352").Interior.ColorIndex = 43 Then
    Range("B2:D352").Interior.ColorIndex = 43
    Range("C2:D352").Interior.ColorIndex = 43
    End If

Next i
Reply With Quote
  #2  
Old 11-14-2019, 12:58 PM
Meshuga's Avatar
Meshuga Meshuga is online now
Member
Non-Actuary
 
Join Date: Dec 2001
Posts: 12,913
Default

what determines the color of the first set of cells?
__________________
I know I don't talk in my sleep. Someone at work would have told me by now.
Reply With Quote
  #3  
Old 11-14-2019, 01:12 PM
mch375 mch375 is offline
CAS SOA
 
Join Date: Mar 2019
College: Postgraduate
Posts: 16
Default Reply to Meshuga

It is entered manually.
Reply With Quote
  #4  
Old 11-14-2019, 01:35 PM
act_123 act_123 is offline
Member
CAS
 
Join Date: Dec 2013
Posts: 4,890
Default

Quote:
Originally Posted by mch375 View Post
I am trying to color cells based on the color of the other cells (standard color light green). After finding out that conditional formatting can't be applied, I wrote the code below but it does not return anything. If there is a better way to write it, I'll be happy to know too. Thank you for your feedback.
Code:
For i = 2 To lastrow
    
    If Range("D2:D352").Interior.ColorIndex = 43 Then
    Range("B2:D352").Interior.ColorIndex = 43
    Range("C2:D352").Interior.ColorIndex = 43
    End If

Next i
Did you try something like this?

Range("B2 : D352").Interior.ColorIndex = Range("D2 : D352").Interior.ColorIndex

ignore the spaces above, I had to do it to avoid the emoticons.
__________________
ACAS 7 8 9
Reply With Quote
  #5  
Old 11-14-2019, 01:42 PM
mch375 mch375 is offline
CAS SOA
 
Join Date: Mar 2019
College: Postgraduate
Posts: 16
Default

Quote:
Originally Posted by act_123 View Post
Did you try something like this?

Range("B2 : D352").Interior.ColorIndex = Range("D2 : D352").Interior.ColorIndex

ignore the spaces above, I had to do it to avoid the emoticons.
Yes, just tried it but the same result. Maybe the colorindex doesn't match the standard light green? I found its RGB and also tried ColorIndex = 43 or 35.

By the way the range is B2:B352 and C2:C352, sorry for the typo.
Reply With Quote
  #6  
Old 11-14-2019, 02:08 PM
act_123 act_123 is offline
Member
CAS
 
Join Date: Dec 2013
Posts: 4,890
Default

is it the font of the cell or the background color?
__________________
ACAS 7 8 9
Reply With Quote
  #7  
Old 11-14-2019, 02:12 PM
llcooljabe's Avatar
llcooljabe llcooljabe is offline
Member
CAS
 
Join Date: Aug 2002
Posts: 23,448
Default

googling leads me to this: https://stackoverflow.com/questions/...get-cell-color

It seems helpful.
__________________
www.GoodNewsNow.info
Propoganda
Reply With Quote
  #8  
Old 11-14-2019, 02:17 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is offline
Member
SOA
 
Join Date: Jul 2014
Posts: 31,536
Default

This: Range("D2:D352").Interior.ColorIndex only returns a value if all the cells in the range are the same color. Otherwise it will return Null
__________________
Quote:
Originally Posted by pete5383 View Post
if you supported Trump, you should be profoundly embarrassed by your poor decision making.
Reply With Quote
  #9  
Old 11-14-2019, 02:19 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is offline
Member
SOA
 
Join Date: Jul 2014
Posts: 31,536
Default

If what you really want to do is set the color of B3 based on D3 and the color of B4 based on D4, you are not doing that right.
__________________
Quote:
Originally Posted by pete5383 View Post
if you supported Trump, you should be profoundly embarrassed by your poor decision making.
Reply With Quote
  #10  
Old 11-14-2019, 02:19 PM
Childish Gambino's Avatar
Childish Gambino Childish Gambino is offline
Member
SOA
 
Join Date: Jul 2014
Posts: 31,536
Default

That loop on i is doing nothing.
__________________
Quote:
Originally Posted by pete5383 View Post
if you supported Trump, you should be profoundly embarrassed by your poor decision making.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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


All times are GMT -4. The time now is 11:39 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.27976 seconds with 11 queries