ExcelBanter - Auto Population data and color

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Auto Population data and color (https://www.excelbanter.com/excel-discussion-misc-queries/269846-auto-population-data-color.html)

Auto Population data and color

Hi,
Alright I am pulling my hair out trying to figure this out. Say I am doing a simple auto populate formula to populate verbatim data from cell C9 to cell C94. I understand this by just typing into C94: =C9. Then anything I type into C9 will automatically show up in C94. now say I were to type something into C9 and then change the fill color of C9. How would I make it so the color I filled in C9 automatically shows up in C94? Thanks for your time folks!

Quote:

Originally Posted by ArchAngelixi (Post 964103)

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

Dear ArchAngelixi, Good Morning.

1) If you must to have at C94 the same value of C9, just use:
....C94 -- =C9

2) If you must to have at C9 the change fill colour dependent of a condition use CONDITIONAL FORMATTING.

a) Select C9
b) Menu -- Format -- Conditional Formatting
Use the conditions that you need and the specific colour that you want.
OK

3) If you must to have at C94 the same change fill colour condition of C9, just do:

a) Select C9
b) Copy
c) Select C94
D) Paste Special -- Format

Tell me if it worked for you.

Quote:

Originally Posted by ArchAngelixi (Post 964103)

A somewhat messy, but effective way is to put in a bit of code to do this for you. The only downfall is that changing the formatting isn't captured as a "Change" by Excel, so only the changing of the cell's value will trigger this piece of code.

Under the Worksheet Change object, paste in the following code for your workbook:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$C$9" Then

        Range("C9").Copy

        Range("C94").PasteSpecial xlPasteFormats

        Range("C9").Activate

    End If

End Sub