+ Reply to Thread
Results 1 to 10 of 10

Prevent copying and pasting Conditional Formatting Rules from one sheet to another sheet

  1. #1
    Registered User
    Join Date
    05-13-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Prevent copying and pasting Conditional Formatting Rules from one sheet to another sheet

    I have a document that contains 31 workssheets. Data that has been input into some of the cells is copyed and pasted from one sheet to another on a daily basis. The problem I have is that when they copy and past this data they do not use the paste special function and choose value only so it is creating redundent Conditional Formatting rules. What can I do in order to prevent this from occuring. The end users are not experienced in Excel, thanks

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    So, just to be clear...they are allowed to copy and paste, just no formatting?
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    05-13-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    They are allowed to copy and paste, yes. They hav full right to the worksheet; basically what they do is enter data on one sheeet then at the end of the day they carry some of it to another sheet for the next day. When they copy and paste a1 from sheet1 to sheet2 it make an additional conditional formating rule on sheet2. It gets componded when you are talking about 31 sheets.

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    Well, the EASIEST way, is when they copy. then the option box shows up on the the paste, select no formatting...,but this sounds like you want to turn the formatting option off altogether...sounds like a VBA solution again...there is NO way to turn the copy/paste options off in Excel formulas...

  5. #5
    Registered User
    Join Date
    05-13-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    Would it be possilbe for you to guide or give me a way to use a vba to prevent this from happening. I am not an expert but i dabble in vba some. It didnt think i have have any other solution other than to use vba. Thanks for clarifying that for me.

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    Better ask the users to paste it as values


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    Well. it is definitely beyond my VBA skills as well, but, I will see if someone else can answer it for you...

  8. #8
    Registered User
    Join Date
    05-13-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    Thank you. It will be appreciated.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    This is actually a pretty simple macro. The hard part will be installing it and attaching a shortcut key to it to do what you want. For instance, you could intercept the CTRL-V for paste function... or you can assign a unique hotkey and teach your people to paste it using that hotkey instead of the CTRL-V.

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  10. #10
    Registered User
    Join Date
    05-13-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Prevent copying and pasting Conditional Formatting Rules from one sheet to another she

    I found a similar thread but I do not know enough to convert it to my situation. The difference is that if column I1 on my sheet = the word complete then the macro would shade the cells in row A1 to I1 to yellow. Basically if i type the word complete or use a drop down list to select the work complete in any cell in the column I it will shade the cells in that row from A1 to I1 yellow.

    Here is the thread info:

    If column B row whatever = todays date then I want the row from col B to col O, to fill in green. I know I can do this with conditional formatting but I have data that updates itself which causes this conditional formatting to go funny every day, so I really need some sort of macro to do it.

    Option Explicit

    Sub HighlightIfDateIsToday()
    Dim rangeToTest As Range
    Dim cell As Range
    Dim dateToTest As Date
    Dim lrow As Long

    lrow = Worksheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    'MsgBox lrow
    Set rangeToTest = Worksheets("Sheet1").Range("B1:B" & lrow)

    dateToTest = Date
    For Each cell In rangeToTest
    If cell.Value = dateToTest Then
    Range(cell, cell.Offset(0, 13)).Interior.Color = 5287936
    End If
    Next cell
    End Sub
    Last edited by jcmoody1993; 05-13-2013 at 06:17 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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