Warning

 

Close
Confirm Action

Are you sure you wish to do this?

Cancel Confirm
5824 Online
bravocompany bravocompany bravocompany
brownells brownells
tnvc tnvc tnvc
AR15.COM
bravocompany
My Account
bravocompany bravocompany bravocompany
brownells brownells
tnvc tnvc tnvc
GS
Guns
Holosun
brownells
bravocompany
cz
geissele
palmetto
springfield
DFC
colt
aim
vortex
magpul
silencerShop
midwest
RS
OSIGHT
primaryArms
JRH
skd
fightlite
5824 Online
cz
Guns
Holosun
palmetto
DFC
primaryArms
geissele
colt
magpul
springfield
OSIGHT
silencerShop
RS
brownells
vortex
aim
GS
midwest
fightlite
bravocompany
JRH
skd
Site News Could You Have Stopped This? Vegas Shooting Analysis with Attorneys on Retainer
3/26/2013 5:44:08 PM EDT
I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges.





IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied.




Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data.




I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet.




Any ideas?
3/26/2013 5:45:22 PM EDT
[#1]
Can you have a few cells for data entry and a few for the formatting?
3/26/2013 5:49:07 PM EDT
[#2]
Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values?  You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output...
3/26/2013 5:51:03 PM EDT
[#3]
Quoted:
I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges.

IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied.

Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data.

I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet.

Any ideas?


How are you cutting and pasting?  Just paste as values or formula only and it won affect your formatting
3/26/2013 5:52:19 PM EDT
[#4]
I'm not a fan of protecting sheets, but I'm wondering if a decent solution would be to create a macro and link it to a simple button like "Apply Formatting" that the user would activate AFTER doing all their manipulation. That said, I don't have the code for you





Here's some stock code to play with:



From: http://stackoverflow.com/questions/13661965/conditional-formatting-using-excel-vba-code


<code>Sub setCondFormat()
    Range("B3").Select
    With Range("B3:H63")
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
          "=IF($D3="""",FALSE,IF($F3>=$E3,TRUE,FALSE))"
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = 5287936
                .TintAndShade = 0
            End With
        End With
    End With
End Sub</code>


 
3/26/2013 5:53:54 PM EDT
[#5]
Quoted:
I'm not a fan of protecting sheets, but I'm wondering if a decent solution would be to create a macro and link it to a simple button like "Apply Formatting" that the user would activate AFTER doing all their manipulation. That said, I don't have the code for you


You don't need code for something that simple.  Just use the record macro option.
3/26/2013 5:56:33 PM EDT
[#6]
Try as another suggested and add a spreadsheet tab in your workbook for data entry and link those data entry cells to your presentation tab with the conditional formatting.  Linking is much easier than copy-cut-paste and doesn't disturb formatting on the linked presentation page.
3/26/2013 5:57:07 PM EDT
[#7]



Quoted:



Quoted:

I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges.





IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied.




Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data.




I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet.




Any ideas?




How are you cutting and pasting?  Just paste as values or formula only and it won affect your formatting


The problem is that "I" know what to do to prevent the excel document from screwing up. But I can't expect Regular Joe from doing what is instinctual to his limited Excel background.
3/26/2013 5:58:05 PM EDT
[#8]



Quoted:


Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values?  You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output...


This makes sense. Thanks. I was just wondering if there was any way to do it on a single spreadsheet.

 
3/26/2013 5:58:11 PM EDT
[#9]
The only thing I can think of off the top of my head is to copy and paste, then delete the contents of the original cells.  Deleting doesn't remove formatting like cut does.  Adding rows should work fine as long as they're inserted within the range of the other formatted rows.

 
3/26/2013 6:01:09 PM EDT
[#10]
I don't use 2010 but I believe protecting the sheet will protect conditional formatting even if the cells are unlocked.
3/26/2013 6:03:59 PM EDT
[#11]



Quoted:


I don't use 2010 but I believe protecting the sheet will protect formatting even if the cells are unlocked.


Leaving the cells unlocked but protecting the sheet to prevent a user from formatting still doesn't protect the "cut" function or pasting from outside of the spreadsheet from ruining the format.



You would think the designers would have thought about this dilemma.
3/26/2013 6:04:13 PM EDT
[#12]
If you want to link to formatted cells without adding another tab, it's easily doable.  And if you need to print your document and don't want to see the section where data is input, hide the rows or columns before printing, then unhide when done to allow users to enter data in the appropriate cells.
3/26/2013 6:06:45 PM EDT
[#13]
Quoted:

Quoted:
Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values?  You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output...

This makes sense. Thanks. I was just wondering if there was any way to do it on a single spreadsheet.  


Yea, have input cells to the side.
3/26/2013 6:10:35 PM EDT
[#14]



Quoted:



Quoted:




Quoted:

Create another worksheet that allows users to enter values that feed into the spreadsheet that has the conditional formatting and values?  You're not going to win with the situation you describe... you can't allow users to manipulate cell formatting and data and expect consistent output...


This makes sense. Thanks. I was just wondering if there was any way to do it on a single spreadsheet.  




Yea, have input cells to the side.


Hah. I will just link spreadsheets. Otherwise one spreadsheet will be too cluttered. Thanks guys.

 
3/26/2013 7:48:56 PM EDT
[#15]
It seems linking between worksheets doesn't work either.



If you cut/delete rows on the editable worksheet, you force a #REF! error because the link is removed. Inserting cells is unrecognized on the linked page.







Again, why did Microsoft overlook this simple problem?
3/26/2013 8:01:15 PM EDT
[#16]
Quoted:
I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges.

IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied.

Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data.

I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet.

Any ideas?

Try "paste special"?  And select "values" from the drop-down, maybe.

Are you trying to preserve format in the cell from which you cut the data, or in the target of the paste?
3/26/2013 9:43:30 PM EDT
[#17]



Quoted:



Quoted:

I have a conditional format set up on Excel 2010 that affects a document. I want users to be able to enter data and manipulate cells. However, the paste/cut/insert cells/delete rows functions (common tools) ruins the conditional formatting ranges.





IE: While routinely fixing up the document, I want to cut a cell's information and move it to another cell. Standard stuff someone might do. But now the cell that was cut is void of any conditional formatting rule and must be reapplied.




Locking/Protecting cells is inconvenient because it prevents someone from manipulating cell data.




I have searched for macros to help with this but come up short. The only thing I can think of doing is disabling cut/copy/paste/insert cells/delete rows/etc. but I'm not savvy enough to do that yet.




Any ideas?


Try "paste special"?  And select "values" from the drop-down, maybe.



Are you trying to preserve format in the cell from which you cut the data, or in the target of the paste?



Both.

 






I think I have found out how to make linking to another worksheet reference dynamically by using the INDIRECT function. So if cells are added/deleted/cut on the original sheet, the second sheet will simply keep track of what it sees versus mapping individual cells.




For instance on Sheet2:




=INDIRECT("Sheet1!C3")




instead of normal linking




=Sheet1!C4




I've been playing with it for a while. Will let you all know how it goes.
3/26/2013 9:54:17 PM EDT
[#18]
You could also try writing an event driven macro that forces the formatting you want on the editable sheet. Sometimes the most direct solution is the best.
3/26/2013 10:41:51 PM EDT
[#19]





Quoted:



You could also try writing an event driven macro that forces the formatting you want on the editable sheet. Sometimes the most direct solution is the best.



How would I do that? EDIT: I'm not too familiar with code/VBA.
3/26/2013 10:45:13 PM EDT
[#20]
You are talking data entry AND cutting and pasting?  You need Access, not Excel.
3/26/2013 11:16:41 PM EDT
[#21]



Quoted:


You are talking data entry AND cutting and pasting?  You need Access, not Excel.


I'm just trying to prevent users from screwing up the conditional formatting on a spreadsheet. I have found the workaround by linking spreadsheets via the INDIRECT formula. One page is protected and for viewing purposes (which has the conditional formatting), the other page is simply data input. They can cut/paste as much as they want.
3/26/2013 11:50:45 PM EDT
[#22]
Quoted:

Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.

I'm just trying to prevent users from screwing up the conditional formatting on a spreadsheet. I have found the workaround by linking spreadsheets via the INDIRECT formula. One page is protected and for viewing purposes (which has the conditional formatting), the other page is simply data input. They can cut/paste as much as they want.


I'm just saying Access is all about data entry and manipulation with strong data protection (tables).  Make the leap from Excel to Access.  You can import your excel data into it.
3/27/2013 12:20:18 AM EDT
[#23]
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.
3/27/2013 12:22:17 AM EDT
[#24]
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.
3/27/2013 12:44:53 AM EDT
[#25]
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.


Access compares well to a skateboard - I guess if you can't use anything else it might get the job done.

3/27/2013 12:54:27 AM EDT
[#26]
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.


Access compares well to a skateboard - I guess if you can't use anything else it might get the job done.



Multiple endusers punching in data is asking for a load of butthurt when using Excel.  That is NOT its purpose.
3/27/2013 12:59:05 AM EDT
[#27]
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.


Access compares well to a skateboard - I guess if you can't use anything else it might get the job done.



Multiple endusers punching in data is asking for a load of butthurt when using Excel.  That is NOT its purpose.


Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all.
3/27/2013 1:01:01 AM EDT
[#28]
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.


Access compares well to a skateboard - I guess if you can't use anything else it might get the job done.



Multiple endusers punching in data is asking for a load of butthurt when using Excel.  That is NOT its purpose.


Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all.


You just MacGuyvered excel into a weak version of Access, but with very little utility and use.  Imagine customizing your input and querying your tables on the fly.  And even further, make nice reports anyone can read.  Nifty!
3/27/2013 1:08:03 AM EDT
[#29]
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.


Access compares well to a skateboard - I guess if you can't use anything else it might get the job done.



Multiple endusers punching in data is asking for a load of butthurt when using Excel.  That is NOT its purpose.


Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all.


You just MacGuyvered excel into a weak version of Access, but with very little utility and use.  Imagine customizing your input and querying your tables on the fly.  And even further, make nice reports anyone can read.  Nifty!


I'm thoroughly familiar with access, I built databases and trouble call tracking systems with it. It's a piece of shit, most people don't have it because it isn't in the default install of office, and excel quite frankly is faster and more flexible for simple tasks like the op is doing.
3/27/2013 1:09:43 AM EDT
[#30]
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.


Access compares well to a skateboard - I guess if you can't use anything else it might get the job done.



Multiple endusers punching in data is asking for a load of butthurt when using Excel.  That is NOT its purpose.


Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all.


You just MacGuyvered excel into a weak version of Access, but with very little utility and use.  Imagine customizing your input and querying your tables on the fly.  And even further, make nice reports anyone can read.  Nifty!


I'm thoroughly familiar with access, I built databases and trouble call tracking systems with it. It's a piece of shit, most people don't have it because it isn't in the default install of office, and excel quite frankly is faster and more flexible for simple tasks like the op is doing.


Access is the shittiest of all databases, but mostly the .mil community is stuck with it.  Excel is nice for simple tasks, but with multiple users in a shared workbook is just asking for trouble.  I've built worksheets since excel existed.
3/27/2013 1:45:51 AM EDT
[#31]
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
Quoted:
You are talking data entry AND cutting and pasting?  You need Access, not Excel.


Excel is perfectly capable of doing what he wants.


So is a skateboard to get to work.


Access compares well to a skateboard - I guess if you can't use anything else it might get the job done.



Multiple endusers punching in data is asking for a load of butthurt when using Excel.  That is NOT its purpose.


Excel can do it just fine. Use a data entry spreadsheet and a separate, formatted spreadsheet that the users don't have access to that pulls the data from the other(s) and formats it. I wrote a couple of spreadsheets to do nearly exactly that for several hundred users and constant data entry on my ship, I think it took me an hour or so to put it together, and I don't actually know VBA at all.


You just MacGuyvered excel into a weak version of Access, but with very little utility and use.  Imagine customizing your input and querying your tables on the fly.  And even further, make nice reports anyone can read.  Nifty!


I'm thoroughly familiar with access, I built databases and trouble call tracking systems with it. It's a piece of shit, most people don't have it because it isn't in the default install of office, and excel quite frankly is faster and more flexible for simple tasks like the op is doing.


Access is the shittiest of all databases, but mostly the .mil community is stuck with it.  Excel is nice for simple tasks, but with multiple users in a shared workbook is just asking for trouble.  I've built worksheets since excel existed.


That's why you don't use shared worksheets.
3/27/2013 3:54:34 AM EDT
[#32]
Have you tried formatting the spreadsheest as a table, then apply your conditional formatting on each individual entire column?  You may also need to instruct users to use the paste value instead of just paste.
3/27/2013 8:28:41 AM EDT
[#33]
=INDIRECT(ADDRESS(ROW()-1,COLUMN()))+INDIRECT(ADDRESS(ROW(),COLUMN()-6))
3/27/2013 3:54:42 PM EDT
[#34]
Alright. I've spent the the last 24 hours (minus maybe about 7 hours of sleep) reading and practicing VBA code.






After a while of mastering INDIRECT functions across two spreadsheets (one for editing, and one for protected viewing), I then moved on to command buttons that caused a very handy sort function (hides all rows that do not contain a highlighted cell date, IE yellow cell for 60 days out and red cell for 30 days out and earlier), and one button to simply unhide all cells. Took a while to get the code right so it would be instant (at first, I was forcing it to perform multiple IF checks cell by cell which took FOREVER).







Then once that was done, I wasn't satisfied with what I accomplished. And heeding advice from someone in here earlier who mentioned to force formatting via a macro on the one page: I played around with it until... badabing, badaboom! Users can now manipulate the data as much as they want without fear of screwing anything up. And I don't have to have a separate linked page or any protected page nonsense.







As a non-desk sitting .mil guy, this is almost like Prometheus delivering fire to mankind. Now I can keep track of my troops' shit easily and pass this along our unit so we aren't re-inventing the wheel every Ops meeting. It's amazing how Office illiterate we are when this could help cut our admin-wasting-time in half.







For anyone interested in what the code is:



EDIT: The reason the hide rows code uses the dates versus color of cell is due to the fact that I couldn't get it to hide based on color back when I was using conditional formatting. It may or may not be possible now that I opted out of conditional formatting for the ChangeEvent highlighting.









Private Sub Worksheet_Change(ByVal Target As Range)
    Dim icolor As Integer
    Dim cell As Range

    If Intersect(Target, Range("C3:T65")) Is Nothing Then Exit Sub

    For Each cell In Target
        icolor = 0
        Select Case cell
            Case "": icolor = 2
            Case Is <= Date + 30: icolor = 3
            Case Is <= Date + 60: icolor = 6
        End Select
        If icolor <> 0 Then cell.Interior.ColorIndex = icolor
    Next cell
End Sub
Private Sub CommandButton1_Click()
CommandButton1.Caption = "Sort"
Rows("3:65").Select
    Selection.EntireRow.Hidden = True
For Each cell In Range("C3:T65")
If cell.Value <= Date + 60 And cell.Value > 0 Then
cell.EntireRow.Hidden = False
End If
Next
End Sub

Private Sub CommandButton2_Click()
CommandButton2.Caption = "Show All Rows"
Rows("3:65").Select
    Selection.EntireRow.Hidden = False
End Sub

 
3/27/2013 4:06:51 PM EDT
[#35]
Congrats! It is amazing what you can do once you set your mind to it and dig in.
3/27/2013 4:07:48 PM EDT
[#36]
congrats man. Sorry I was too lazy to help with the code. I'm kind of in your shoes --- know just enough to be dangerous, but VBA code is a giant game of trial and error (and modifying someone elses existing code) for me.
3/27/2013 4:27:51 PM EDT
[#37]
Thanks guys. The functions seem so simple now that everything is made easy for the user. But, damn, it took a lot of work.
 
 
 
 
ar15com
LAPG
silencerShop
fightlite
LAPG
gray
Pro2A
geissele
cmmg
brownells
primaryArms
palmetto
Faxxon
RS
ar15com
blackhills
ar15com
springfield
CA
colt
bravocompany
SAS