Rabu, 27 September 2017

Re: [ExcelVBA] Colored Cells

 

Hi Paul....


Coming to this late so sorry if I'm out of line... please tell me to bug out if I am!!!!

I personally would favour a loop through a built array.
THis would centralize the colours and so on so they were updateable in one place.
They could even be picked up off another or the same sheet... giving a little code independence to the colours chosen
Choose a colour on the sheet and that's the colour it becomes.
Add as many codes as you want even.... all outside of the code... Once it's set up... hehehehe

HTH
Lisa



-----Original Message-----
From: Paul Schreiner schreiner_paul@att.net [ExcelVBA] <ExcelVBA@yahoogroups.com>
To: ExcelVBA <ExcelVBA@yahoogroups.com>
Sent: Wed, Sep 27, 2017 1:59 pm
Subject: Re: [ExcelVBA] Colored Cells

Absolutely!I'd probably use a Change Event to trigger evaluating a change.Then, simply record a macro changing the colors so you know what the values are and use something like: Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)
    Dim Targ As Range
    For Each Targ In Target
        If (Targ.Column = 2) Then 'Process only changes to column B
            Select Case UCase(Targ.Value)
                Case "JO"
                    With Targ.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent2
                        .TintAndShade = 0.799981688894314
                        .PatternTintAndShade = 0
                    End With
                Case "KB"
                    With Targ.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent3
                        .TintAndShade = 0.799981688894314
                        .PatternTintAndShade = 0
                    End With
                Case "LC"
                    With Targ.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent4
                        .TintAndShade = 0.799981688894314
                        .PatternTintAndShade = 0
                    End With
                Case "KG"
                    With Targ.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent5
                        .TintAndShade = 0.799981688894314
                        .PatternTintAndShade = 0
                    End With
                Case "HSE"
                    With Targ.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorDark2
                        .TintAndShade = -9.99786370433668E-02
                        .PatternTintAndShade = 0
                    End With
                Case Else
                    With Targ.Interior
                        .Pattern = xlNone
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                    End With
            End Select
        End If
    Next Targ
End Sub
Paul-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

On Tuesday, September 26, 2017 9:41 PM, "Sharron Puryear sheizageek@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:


  Thanks. I would prefer it to be in VBA.  I appreciate your time and your file.  I am well aware that you can do almost anything in VBA.  I am just brain drained right now.  I am ok with conditional formatting, but I just prefer it in VBA if I can put it there.
Thanks again.
Sharron

From: "Paul Schreiner schreiner_paul@att.net [ExcelVBA]" <ExcelVBA@yahoogroups.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Tuesday, September 26, 2017 9:39 AM
Subject: Re: [ExcelVBA] Colored Cells

  You CAN do it with VBA, but you can also do it with Conditional formatting.The fact that you asked the question says to me that you probably don't have a lot of experience with VBA (or you'd know you can do almost ANYTHING with VBA!)
you might be more comfortable with the Conditional Formatting approach.Attached I have a sample file that highlights a single cell or three cells based on the initials in column B.
take a look and see if you can follow..
Paul-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

On Monday, September 25, 2017 7:16 PM, "Sharron Puryear sheizageek@yahoo.com [ExcelVBA]" <ExcelVBA@yahoogroups.com> wrote:


  Good afternoon David.
I am hoping my issue is a simple one (for you anyhow).
I have 3 colums in a spreadsheet. . . Date, Initials and Amount.
There are 5 sales persons.  Initials are JO, KB, LC, KG and Hse.
I would like my page to automatically add a color to the cell for each corresponding initials.
Is this possible in VBA?
Thanks
Sharron Puryear #yiv7989396421 #yiv7989396421 -- #yiv7989396421ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7989396421 #yiv7989396421ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7989396421 #yiv7989396421ygrp-mkp #yiv7989396421hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7989396421 #yiv7989396421ygrp-mkp #yiv7989396421ads {margin-bottom:10px;}#yiv7989396421 #yiv7989396421ygrp-mkp .yiv7989396421ad {padding:0 0;}#yiv7989396421 #yiv7989396421ygrp-mkp .yiv7989396421ad p {margin:0;}#yiv7989396421 #yiv7989396421ygrp-mkp .yiv7989396421ad a {color:#0000ff;text-decoration:none;}#yiv7989396421 #yiv7989396421ygrp-sponsor #yiv7989396421ygrp-lc {font-family:Arial;}#yiv7989396421 #yiv7989396421ygrp-sponsor #yiv7989396421ygrp-lc #yiv7989396421hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7989396421 #yiv7989396421ygrp-sponsor #yiv7989396421ygrp-lc .yiv7989396421ad {margin-bottom:10px;padding:0 0;}#yiv7989396421 #yiv7989396421actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7989396421 #yiv7989396421activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7989396421 #yiv7989396421activity span {font-weight:700;}#yiv7989396421 #yiv7989396421activity span:first-child {text-transform:uppercase;}#yiv7989396421 #yiv7989396421activity span a {color:#5085b6;text-decoration:none;}#yiv7989396421 #yiv7989396421activity span span {color:#ff7900;}#yiv7989396421 #yiv7989396421activity span .yiv7989396421underline {text-decoration:underline;}#yiv7989396421 .yiv7989396421attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7989396421 .yiv7989396421attach div a {text-decoration:none;}#yiv7989396421 .yiv7989396421attach img {border:none;padding-right:5px;}#yiv7989396421 .yiv7989396421attach label {display:block;margin-bottom:5px;}#yiv7989396421 .yiv7989396421attach label a {text-decoration:none;}#yiv7989396421 blockquote {margin:0 0 0 4px;}#yiv7989396421 .yiv7989396421bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7989396421 .yiv7989396421bold a {text-decoration:none;}#yiv7989396421 dd.yiv7989396421last p a {font-family:Verdana;font-weight:700;}#yiv7989396421 dd.yiv7989396421last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7989396421 dd.yiv7989396421last p span.yiv7989396421yshortcuts {margin-right:0;}#yiv7989396421 div.yiv7989396421attach-table div div a {text-decoration:none;}#yiv7989396421 div.yiv7989396421attach-table {width:400px;}#yiv7989396421 div.yiv7989396421file-title a, #yiv7989396421 div.yiv7989396421file-title a:active, #yiv7989396421 div.yiv7989396421file-title a:hover, #yiv7989396421 div.yiv7989396421file-title a:visited {text-decoration:none;}#yiv7989396421 div.yiv7989396421photo-title a, #yiv7989396421 div.yiv7989396421photo-title a:active, #yiv7989396421 div.yiv7989396421photo-title a:hover, #yiv7989396421 div.yiv7989396421photo-title a:visited {text-decoration:none;}#yiv7989396421 div#yiv7989396421ygrp-mlmsg #yiv7989396421ygrp-msg p a span.yiv7989396421yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7989396421 .yiv7989396421green {color:#628c2a;}#yiv7989396421 .yiv7989396421MsoNormal {margin:0 0 0 0;}#yiv7989396421 o {font-size:0;}#yiv7989396421 #yiv7989396421photos div {float:left;width:72px;}#yiv7989396421 #yiv7989396421photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv7989396421 #yiv7989396421photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7989396421 #yiv7989396421reco-category {font-size:77%;}#yiv7989396421 #yiv7989396421reco-desc {font-size:77%;}#yiv7989396421 .yiv7989396421replbq {margin:4px;}#yiv7989396421 #yiv7989396421ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7989396421 #yiv7989396421ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7989396421 #yiv7989396421ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7989396421 #yiv7989396421ygrp-mlmsg select, #yiv7989396421 input, #yiv7989396421 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7989396421 #yiv7989396421ygrp-mlmsg pre, #yiv7989396421 code {font:115% monospace;}#yiv7989396421 #yiv7989396421ygrp-mlmsg * {line-height:1.22em;}#yiv7989396421 #yiv7989396421ygrp-mlmsg #yiv7989396421logo {padding-bottom:10px;}#yiv7989396421 #yiv7989396421ygrp-msg p a {font-family:Verdana;}#yiv7989396421 #yiv7989396421ygrp-msg p#yiv7989396421attach-count span {color:#1E66AE;font-weight:700;}#yiv7989396421 #yiv7989396421ygrp-reco #yiv7989396421reco-head {color:#ff7900;font-weight:700;}#yiv7989396421 #yiv7989396421ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7989396421 #yiv7989396421ygrp-sponsor #yiv7989396421ov li a {font-size:130%;text-decoration:none;}#yiv7989396421 #yiv7989396421ygrp-sponsor #yiv7989396421ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7989396421 #yiv7989396421ygrp-sponsor #yiv7989396421ov ul {margin:0;padding:0 0 0 8px;}#yiv7989396421 #yiv7989396421ygrp-text {font-family:Georgia;}#yiv7989396421 #yiv7989396421ygrp-text p {margin:0 0 1em 0;}#yiv7989396421 #yiv7989396421ygrp-text tt {font-size:120%;}#yiv7989396421 #yiv7989396421ygrp-vital ul li:last-child {border-right:none !important;}#yiv7989396421

[Non-text portions of this message have been removed]



#yiv8325303507 #yiv8325303507 -- #yiv8325303507ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8325303507 #yiv8325303507ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8325303507 #yiv8325303507ygrp-mkp #yiv8325303507hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv8325303507 #yiv8325303507ygrp-mkp #yiv8325303507ads {margin-bottom:10px;}#yiv8325303507 #yiv8325303507ygrp-mkp .yiv8325303507ad {padding:0 0;}#yiv8325303507 #yiv8325303507ygrp-mkp .yiv8325303507ad p {margin:0;}#yiv8325303507 #yiv8325303507ygrp-mkp .yiv8325303507ad a {color:#0000ff;text-decoration:none;}#yiv8325303507 #yiv8325303507ygrp-sponsor #yiv8325303507ygrp-lc {font-family:Arial;}#yiv8325303507 #yiv8325303507ygrp-sponsor #yiv8325303507ygrp-lc #yiv8325303507hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8325303507 #yiv8325303507ygrp-sponsor #yiv8325303507ygrp-lc .yiv8325303507ad {margin-bottom:10px;padding:0 0;}#yiv8325303507 #yiv8325303507actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8325303507 #yiv8325303507activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8325303507 #yiv8325303507activity span {font-weight:700;}#yiv8325303507 #yiv8325303507activity span:first-child {text-transform:uppercase;}#yiv8325303507 #yiv8325303507activity span a {color:#5085b6;text-decoration:none;}#yiv8325303507 #yiv8325303507activity span span {color:#ff7900;}#yiv8325303507 #yiv8325303507activity span .yiv8325303507underline {text-decoration:underline;}#yiv8325303507 .yiv8325303507attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv8325303507 .yiv8325303507attach div a {text-decoration:none;}#yiv8325303507 .yiv8325303507attach img {border:none;padding-right:5px;}#yiv8325303507 .yiv8325303507attach label {display:block;margin-bottom:5px;}#yiv8325303507 .yiv8325303507attach label a {text-decoration:none;}#yiv8325303507 blockquote {margin:0 0 0 4px;}#yiv8325303507 .yiv8325303507bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv8325303507 .yiv8325303507bold a {text-decoration:none;}#yiv8325303507 dd.yiv8325303507last p a {font-family:Verdana;font-weight:700;}#yiv8325303507 dd.yiv8325303507last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8325303507 dd.yiv8325303507last p span.yiv8325303507yshortcuts {margin-right:0;}#yiv8325303507 div.yiv8325303507attach-table div div a {text-decoration:none;}#yiv8325303507 div.yiv8325303507attach-table {width:400px;}#yiv8325303507 div.yiv8325303507file-title a, #yiv8325303507 div.yiv8325303507file-title a:active, #yiv8325303507 div.yiv8325303507file-title a:hover, #yiv8325303507 div.yiv8325303507file-title a:visited {text-decoration:none;}#yiv8325303507 div.yiv8325303507photo-title a, #yiv8325303507 div.yiv8325303507photo-title a:active, #yiv8325303507 div.yiv8325303507photo-title a:hover, #yiv8325303507 div.yiv8325303507photo-title a:visited {text-decoration:none;}#yiv8325303507 div#yiv8325303507ygrp-mlmsg #yiv8325303507ygrp-msg p a span.yiv8325303507yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv8325303507 .yiv8325303507green {color:#628c2a;}#yiv8325303507 .yiv8325303507MsoNormal {margin:0 0 0 0;}#yiv8325303507 o {font-size:0;}#yiv8325303507 #yiv8325303507photos div {float:left;width:72px;}#yiv8325303507 #yiv8325303507photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv8325303507 #yiv8325303507photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv8325303507 #yiv8325303507reco-category {font-size:77%;}#yiv8325303507 #yiv8325303507reco-desc {font-size:77%;}#yiv8325303507 .yiv8325303507replbq {margin:4px;}#yiv8325303507 #yiv8325303507ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv8325303507 #yiv8325303507ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv8325303507 #yiv8325303507ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv8325303507 #yiv8325303507ygrp-mlmsg select, #yiv8325303507 input, #yiv8325303507 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv8325303507 #yiv8325303507ygrp-mlmsg pre, #yiv8325303507 code {font:115% monospace;}#yiv8325303507 #yiv8325303507ygrp-mlmsg * {line-height:1.22em;}#yiv8325303507 #yiv8325303507ygrp-mlmsg #yiv8325303507logo {padding-bottom:10px;}#yiv8325303507 #yiv8325303507ygrp-msg p a {font-family:Verdana;}#yiv8325303507 #yiv8325303507ygrp-msg p#yiv8325303507attach-count span {color:#1E66AE;font-weight:700;}#yiv8325303507 #yiv8325303507ygrp-reco #yiv8325303507reco-head {color:#ff7900;font-weight:700;}#yiv8325303507 #yiv8325303507ygrp-reco {margin-bottom:20px;padding:0px;}#yiv8325303507 #yiv8325303507ygrp-sponsor #yiv8325303507ov li a {font-size:130%;text-decoration:none;}#yiv8325303507 #yiv8325303507ygrp-sponsor #yiv8325303507ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv8325303507 #yiv8325303507ygrp-sponsor #yiv8325303507ov ul {margin:0;padding:0 0 0 8px;}#yiv8325303507 #yiv8325303507ygrp-text {font-family:Georgia;}#yiv8325303507 #yiv8325303507ygrp-text p {margin:0 0 1em 0;}#yiv8325303507 #yiv8325303507ygrp-text tt {font-size:120%;}#yiv8325303507 #yiv8325303507ygrp-vital ul li:last-child {border-right:none !important;}#yiv8325303507



[Non-text portions of this message have been removed]



------------------------------------
Posted by: Paul Schreiner <schreiner_paul@att.net>
------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

__._,_.___
Posted by: Green <1z@compuserve.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (5)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

.

__,_._,___
Diposting oleh EXCEL FEED LEARNING di 10.01

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)