Stuart
12-27-2013, 01:24 PM
Alright, I realize that this is a fairly commonly asked question, but I've tried my best to figure this out on my own.
I am creating a protected worksheet for my team to use. So far, this has been a hack and slash job as I am far from proficient in Excel, but I have managed to make it work to some extent. However, I have realized that many individuals are unable to follow simple instructions, and thus, keep "breaking" my spread sheet. Thus, I've set off to create a "fool proof" system to avoid these problems.
The sheet seeks to track how long a user spent on particular pieces of data. There are multiple types of documents to track.
One column, F, specifies the type of document to enter. Users enter a string value of VD, RM, FA, NFA, or QC, or some combination of them.
If the user has put FA or NFA in column F, I need that entire row to be highlighted. The color I need is "16764108"
Sub Highlight_Apps()
'
' Highlight_Apps Macro
'
' Keyboard Shortcut: Ctrl+r
'
Cells.FormatConditions.Delete
Range("A6:J205").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(NOT(ISERROR(SEARCH(""FA"", F6:F205))))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 16764108
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
This code *works*... but I am only seeing the corresponding cell in column A receive the proper color. The rest remain unchanged.
Formatting needs to apply from A6:J205 (A1:J5 are just a header and other necessary calculations that are currently functioning).
I understand that with this macro I will have to ask users to enable macros as well as press a key combination (I've been using CTRL + R) prior to printing, but I can only hand hold so much.
I appreciate any and all help on this matter! I am leaving the office soon, but I will try to answer from home as well.
Note:
Sometimes not every cell will be filled in within the row, which from what I have read may make this more complicated, please keep this in mind if this changes your code.
I am creating a protected worksheet for my team to use. So far, this has been a hack and slash job as I am far from proficient in Excel, but I have managed to make it work to some extent. However, I have realized that many individuals are unable to follow simple instructions, and thus, keep "breaking" my spread sheet. Thus, I've set off to create a "fool proof" system to avoid these problems.
The sheet seeks to track how long a user spent on particular pieces of data. There are multiple types of documents to track.
One column, F, specifies the type of document to enter. Users enter a string value of VD, RM, FA, NFA, or QC, or some combination of them.
If the user has put FA or NFA in column F, I need that entire row to be highlighted. The color I need is "16764108"
Sub Highlight_Apps()
'
' Highlight_Apps Macro
'
' Keyboard Shortcut: Ctrl+r
'
Cells.FormatConditions.Delete
Range("A6:J205").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(NOT(ISERROR(SEARCH(""FA"", F6:F205))))"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 16764108
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
This code *works*... but I am only seeing the corresponding cell in column A receive the proper color. The rest remain unchanged.
Formatting needs to apply from A6:J205 (A1:J5 are just a header and other necessary calculations that are currently functioning).
I understand that with this macro I will have to ask users to enable macros as well as press a key combination (I've been using CTRL + R) prior to printing, but I can only hand hold so much.
I appreciate any and all help on this matter! I am leaving the office soon, but I will try to answer from home as well.
Note:
Sometimes not every cell will be filled in within the row, which from what I have read may make this more complicated, please keep this in mind if this changes your code.