Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #1  
Old 06-12-2013, 02:12 PM
Phil Payne Phil Payne is offline VBA to immediately change the colour of a cell depending on the code placed in anothe Windows XP VBA to immediately change the colour of a cell depending on the code placed in anothe Office XP
Novice
VBA to immediately change the colour of a cell depending on the code placed in anothe
 
Join Date: Apr 2013
Location: Glasgow, Scotland
Posts: 5
Phil Payne is on a distinguished road
Default VBA to immediately change the colour of a cell depending on the code placed in anothe

Hello,



Firstly please let me advise that standard conditional formatting will not work here as this requires ten conditions to be set.

I have a worksheet that contains 40 'paired' columns. In the first column of each pair I can enter any one of ten condition codes. The corresponding cell in the second of the paired columns needs to change its interior colour to that dictated by the code in the first of the pair.

I have tried a few ways without success with my last attempt almost making it where I used three named ranges and this code:
Code:
Dim conditions()
    ReDim conditions(1 To Range("conditions2use").Count)
    Dim i
    i = 1
    For Each cell In Range("conditions2use")
        conditions(i) = CInt(cell.Value)
        i = i + 1
    Next cell
 
    i = 1
    For Each cell In Range("data2use")
        Range("formats2use").Cells(conditions(i)).Select
        Selection.Copy
        cell.Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        i = i + 1
    Next cell
Unfortunately this hit the limit on the number of separate columns I could place within one named range and the macro only ran through 4 of the paired columns - even if this had been successful it would still have been unsatisfactory as a macro had to be run to refresh the cells colours. I wish to have the colour change as the code is changed.

I hope someone in this forum has had a similar requirement in the past and can provide a solution.

I am using MS Excel 2003 SR3.

Thanks in anticipation
Reply With Quote
 

Tags
condition paste vba, formatting cells from vba



Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA to immediately change the colour of a cell depending on the code placed in anothe Quadrant colour based on cell value RoyLittle0 Excel 2 05-05-2013 12:50 AM
VBA to immediately change the colour of a cell depending on the code placed in anothe Change font colour when tasks are completed meileetan Project 3 09-12-2012 07:09 AM
VBA to immediately change the colour of a cell depending on the code placed in anothe Change Colour Theme in Word with VBA Davidoff78 Word VBA 1 06-28-2012 05:23 PM
CHange colour of footer if a cell changes to red OTPM Excel 0 05-26-2011 07:15 AM
contacts email by colour code mikemans Outlook 0 10-31-2009 01:47 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:11 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft