1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Conditional Format, Excel 2007 Help

Discussion in 'Business Applications' started by irvinbang, Mar 26, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    This file is from Excel 2007
    This is about Conditional Formatting Help
    Test file is attached

    My question is how can i make row three work for the rest of the rows?

    A3:A10000 is a number from 1-5
    B:M is just ramdom information that i will be Pasting in

    Explination:

    A3 has a number from 1-5. Cell has CF so that if 1-5 is entered that number will be a certain color.
    Once number is in A3 the other cells B3:M3 will change that same color.

    Now if i put any of the numbers 1-5 the cells B3:M3 will change what ever color number is in A3.

    With that being said if i take and highlight B3:M3 and take the little small box down at the bottom right hand of the last cell, click and drag down to duplicate the CF, all the cells now refer to A3.

    I would like for Each row from A3:A10000 to be able to put the number 1-5 and all the cells with that row B:M to change to the same color.

    I have row 3 set up like i want. For example just change the number in A3 from 1 through 5 and you will see what im talking about.

    Now i can go in and change the CF for each row but i dont have that kinda time to go from A3:A10000

    If anyone can help, Thanks in advance.
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,294
    First Name:
    Wayne
    remove the $ sign from row 3 D-M
    and also the range it applies to - remove the $

    then copy D3-M3 and then select
    D3-M10000 and paste special - format

    the $ is making it stay on A3 - by removing the $ when you copy it moves with the row

    Not sure , but looking in the row 3 - you have some formulas that refer to A2-A22 ???

    so in A3 it changes colour
    then in B3-M3 you want the formula to test for the value in A3 only - in fact use A$3 then you copy from B to M
    no $s
    then copy - paste special the format into all the other cells


    I think i have done it for you here - see attached
     

    Attached Files:

  3. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Maybe i should have been using the copy and paste rather than clicking the bottom right box to drag down.

    As far as the $ being in front i cant remove them. As soon as i delete them and click ok or apply excel puts them right back in there.

    Now i have to take an put in the accual information and see if it still works with the copy and paste.

    Well let you know by tomorrow.
     
  4. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    If you have other plans for Row 1 and 2, append
    *(row()>2)
    to all CFs.

    If you need to insert row, do an insert and then fill the previous row down.

    Copy and paste more than one column at the same time might "duplicate" the CFs, if that is the case do copy and paste column by column.

    Copying and pasting up and down a single column will not affect the CF.

    If there is a need to replace mass amount of data, highlight a (whole) row that is not affected by the change and do a format paint.
    Make sure when you "paste" paint, you include the original row that you copy.
     

    Attached Files:

  5. irvinbang

    irvinbang Thread Starter

    Joined:
    Jan 25, 2013
    Messages:
    85
    Worked good. sorry it took me so long to get it working. Have been very busy at work.
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Conditional Format
  1. Squashman
    Replies:
    5
    Views:
    352
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1094336

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice