+ Reply to Thread
Results 1 to 5 of 5

Cell contents updating colour of other cells

  1. #1
    Registered User
    Join Date
    10-26-2013
    Location
    London, England
    MS-Off Ver
    Excel 2000
    Posts
    4

    Cell contents updating colour of other cells

    Hi there

    I know that I can achieve what I'm trying to do pretty easily in later versions of Excel using conditional formatting, but i'm apparently making life hard for myself and trying to do this in Excel 2000.

    excel.JPG

    How I want the above set up is as follows:
    When I enter the letter a, e, t o or I into a cell between H5 and H70, the cells in that row change colour accordingly. Below is the code i'm using:

    Please Login or Register  to view this content.
    Now I know that I could technically just copy and paste this 70 times and use the same code for each of the 70 rows I want it to apply to, but surely there's a quicker way?

    Possibly an incredibly novice question but hey, I'm nothing if not a novice :P

    Hope you can help

    Moderators note: Code tags added for you -- this time
    Last edited by TokyoClownAttack; 10-27-2013 at 08:17 AM. Reason: Problem is solved

  2. #2
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Cell contents updating colour of other cells

    Hello TokyoClownAttack,

    Would the attached Sample Workbook be what you are after?

    Hope it works in Excel 2000.

    Regards
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,899

    Re: Cell contents updating colour of other cells

    Hi and welcome to the forum

    Sorry to "pound" on you with your 1st post, but Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I will add then for you this time, please remember to use them in future

    Also...
    Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    10-26-2013
    Location
    London, England
    MS-Off Ver
    Excel 2000
    Posts
    4

    Re: Cell contents updating colour of other cells

    Hi again

    Thanks for the help Winon, that almost completely solved the problem.

    Please Login or Register  to view this content.
    The Target.EntireRow bit solved the problem I was initially having. The only thing I had to change is the Range("D5:J5") bit, as whenever I entered a value in the H column, it would always change the colour of the D row as well, regardless of what was already in there. I just changed Range("D5:J5") to Target and that fixed it.

    Thanks a lot for the help and sorry for the rule breaking

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,077

    Re: Cell contents updating colour of other cells

    Hi TokyoClownAttack,

    Glad I could be of some assistance, and I am happy to see that you managed to solve your issue.

    You are a fast learner, wrapping of Code, and Marking your Thread as Solved as well. That's good.

    Thank you also for adding to my Reputation.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to clear only the contents of the cells which have a specific font colour
    By Stellakyp in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-05-2013, 11:01 AM
  2. [SOLVED] Testing colour of cell and result cell not updating automatically
    By BishBosh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-11-2013, 08:55 AM
  3. Macro that clears contents of all cells of certain colour (colour 35) in all sheets
    By Hanskubansku in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-25-2012, 07:33 AM
  4. Updating a cell based on contents of two other cells
    By jonco in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-04-2006, 07:50 AM
  5. [SOLVED] search for a cell colour and print the cells contents in a new she
    By Paul in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-21-2006, 06:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1