| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional format
How can I create a function that will highlight a cell in Column B if the
cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
1. Select the Column B
2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
I forgot to say I am using Excel 2007. Where do I find the conditional
format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a
formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
This may be more useful than CF to uncolor the range and then color for
matches on the right. Sub highlightmatches() lr = Cells(Rows.Count, 1).End(xlUp).Row Cells(1, 1).Resize(lr, 3).Interior.ColorIndex = 0 For i = 1 To lr ml = Len(Cells(i, "d")) For j = 1 To lr If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then Cells(j, "b").Interior.ColorIndex = 6 End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
I'm not getting it to work.
Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
--Select the Column B
--Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
I got that far, but dont know how to write the formula. Thanks!
-- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
--In the text box ''Format values where the values are true'..copy and paste
the below =FIND(D1,B1) --Click Format ButtonPattern and select your color (say Red) -- Hit OK -- If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I got that far, but dont know how to write the formula. Thanks! -- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
That didn't work either. It highlighted the column from the next row down.
Thanks for the help! -- narp "Jacob Skaria" wrote: --In the text box ''Format values where the values are true'..copy and paste the below =FIND(D1,B1) --Click Format ButtonPattern and select your color (say Red) -- Hit OK -- If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I got that far, but dont know how to write the formula. Thanks! -- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
|
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
|
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
I copied and pasted the macro in excel but it highlight anything.
-- narp "Don Guillett" wrote: This may be more useful than CF to uncolor the range and then color for matches on the right. Sub highlightmatches() lr = Cells(Rows.Count, 1).End(xlUp).Row Cells(1, 1).Resize(lr, 3).Interior.ColorIndex = 0 For i = 1 To lr ml = Len(Cells(i, "d")) For j = 1 To lr If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then Cells(j, "b").Interior.ColorIndex = 6 End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
If you're new to macros, you may want to read David McRitchie's intro
at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Use the later version I posted Copy into a regular or sheet module and execute from the sheet where your data is. Assign to a button or shape for convenience. -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... Sorry no, I didn't understand what it was or how to use it. Do I copy and paste to VBA to use it? -- narp "Don Guillett" wrote: Did you try my macro? -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... That didn't work either. It highlighted the column from the next row down. Thanks for the help! -- narp "Jacob Skaria" wrote: --In the text box ''Format values where the values are true'..copy and paste the below =FIND(D1,B1) --Click Format ButtonPattern and select your color (say Red) -- Hit OK -- If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I got that far, but dont know how to write the formula. Thanks! -- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
I opened my spreadsheet (saved as a macro embedded), copied your code to a
VBA screen, saved, and told it to run but it didn't change anything. May I send you my file and have you look at it to see what I am doing wrong? Thank you so much for your help! -- narp "Don Guillett" wrote: Modified for your data. Try it Sub highlightmatches() Dlr = Cells(Rows.Count, "D").End(xlUp).Row blr = Cells(Rows.Count, "B").End(xlUp).Row Cells(1, 1).Resize(blr, 3).Interior.ColorIndex = 0 For i = 1 To Dlr ml = Len(Cells(i, "d")) For j = 1 To blr If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then Cells(j, "b").Interior.ColorIndex = 6 End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Did you try my macro? -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... That didn't work either. It highlighted the column from the next row down. Thanks for the help! -- narp "Jacob Skaria" wrote: --In the text box ''Format values where the values are true'..copy and paste the below =FIND(D1,B1) --Click Format ButtonPattern and select your color (say Red) -- Hit OK -- If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I got that far, but dont know how to write the formula. Thanks! -- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
Send to my address below along with a copy of THIS msg. -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... I opened my spreadsheet (saved as a macro embedded), copied your code to a VBA screen, saved, and told it to run but it didn't change anything. May I send you my file and have you look at it to see what I am doing wrong? Thank you so much for your help! -- narp "Don Guillett" wrote: Modified for your data. Try it Sub highlightmatches() Dlr = Cells(Rows.Count, "D").End(xlUp).Row blr = Cells(Rows.Count, "B").End(xlUp).Row Cells(1, 1).Resize(blr, 3).Interior.ColorIndex = 0 For i = 1 To Dlr ml = Len(Cells(i, "d")) For j = 1 To blr If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then Cells(j, "b").Interior.ColorIndex = 6 End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Did you try my macro? -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... That didn't work either. It highlighted the column from the next row down. Thanks for the help! -- narp "Jacob Skaria" wrote: --In the text box ''Format values where the values are true'..copy and paste the below =FIND(D1,B1) --Click Format ButtonPattern and select your color (say Red) -- Hit OK -- If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I got that far, but dont know how to write the formula. Thanks! -- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
I had refeneced J's in one of my replys and when I change all the j's to DJ's
it works!!! Yeah!!! Thank you soooo much!!!! :) -- narp "Don Guillett" wrote: Modified for your data. Try it Sub highlightmatches() Dlr = Cells(Rows.Count, "D").End(xlUp).Row blr = Cells(Rows.Count, "B").End(xlUp).Row Cells(1, 1).Resize(blr, 3).Interior.ColorIndex = 0 For i = 1 To Dlr ml = Len(Cells(i, "d")) For j = 1 To blr If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then Cells(j, "b").Interior.ColorIndex = 6 End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Did you try my macro? -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... That didn't work either. It highlighted the column from the next row down. Thanks for the help! -- narp "Jacob Skaria" wrote: --In the text box ''Format values where the values are true'..copy and paste the below =FIND(D1,B1) --Click Format ButtonPattern and select your color (say Red) -- Hit OK -- If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I got that far, but dont know how to write the formula. Thanks! -- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
We are glad you got the results desired.
-- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... I had refeneced J's in one of my replys and when I change all the j's to DJ's it works!!! Yeah!!! Thank you soooo much!!!! :) -- narp "Don Guillett" wrote: Modified for your data. Try it Sub highlightmatches() Dlr = Cells(Rows.Count, "D").End(xlUp).Row blr = Cells(Rows.Count, "B").End(xlUp).Row Cells(1, 1).Resize(blr, 3).Interior.ColorIndex = 0 For i = 1 To Dlr ml = Len(Cells(i, "d")) For j = 1 To blr If Right(Cells(j, "b"), ml) = CStr(Cells(i, "d")) Then Cells(j, "b").Interior.ColorIndex = 6 End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... Did you try my macro? -- Don Guillett Microsoft MVP Excel SalesAid Software "narp" wrote in message ... That didn't work either. It highlighted the column from the next row down. Thanks for the help! -- narp "Jacob Skaria" wrote: --In the text box ''Format values where the values are true'..copy and paste the below =FIND(D1,B1) --Click Format ButtonPattern and select your color (say Red) -- Hit OK -- If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I got that far, but dont know how to write the formula. Thanks! -- narp "Jacob Skaria" wrote: --Select the Column B --Conditional FormattingHighlight Cell rulesMore rules --Selet the last option from the list 'Use a formula to determine which cells to format' If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I'm not getting it to work. Here is my data sample: Date Number c Processing 06/14/09 EC17251 34002 07/20/09 EC17570 17251 08/07/09 EC17716 07/15/09 OR33889 07/16/09 OR33956 07/17/09 OR34002 07/22/09 OR34178 I went to the New Formatting Rule as directed but don't know what to enter in the "Format values where this formula is true:" box. I want it to lookup info in column D and if it is found in column B then highlight that cell. For example, Does a cell in Column B contain the info that is found in any cell in column D--otherwise written as: does 34002 (cell D2) show in any cell in column B (cell B7) and if so highlight cell B7. Sorry for the confusion. Thank you for your assistance. -- narp "Jacob Skaria" wrote: Goto Home tabStylesConditional FormattingManage rulesNew ruleUse a formula to determine which cells to format If this post helps click Yes --------------- Jacob Skaria "narp" wrote: I forgot to say I am using Excel 2007. Where do I find the conditional format that allows for multiple conditions. Also to clarify, I want to look for info in cell J2 within the range of column b (b1.b200). -- narp "Jacob Skaria" wrote: 1. Select the Column B 2. From menu FormatConditional Formatting 3. For Condition1Select 'Formula Is' and enter the below formula =FIND(D1,B1) 4. Click Format ButtonPattern and select your color (say Red) 5. Hit OK If this post helps click Yes --------------- Jacob Skaria "narp" wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
Hi,
You may use this formula in conditional formatting =ISNUMBER(LOOKUP(2,1/SEARCH(D20,$B$20:$B$22))). Apply a format of your choice and thencopy paste the conditional formatting down. Do let me know how this works -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "narp" wrote in message ... How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 -- narp |
|
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional format
Assuming that A2:D4 contains the data, try...
1) Select B2 2) Format Conditional Formatting Formula Is If the relevant information for Column B is always the last 3 characters: =MATCH(RIGHT($B2,3)+0,$D$2:$D$4,0) Otherwise: =LOOKUP(9.99999999999999E+307,FIND($D$2:$D$4,$B2)) 3) Choose 'Format' 4) Select the desired formatting 5) Click Ok twice 6) Copy the formatting to the other cells... Copy Paste Special Formats or Format Painter -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , narp wrote: How can I create a function that will highlight a cell in Column B if the cell info contains the info in Column D? Info in Column D is not always aligned with info in Column B. A B C D 06/17/09 OR123 1.00 123 07/01/09 EC458 2.00 528 07/01/09 OR528 15.00 924 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Capture conditional format as cell format | Excel Discussion (Misc queries) | |||
| New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
| Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
| copy conditional format to regular format | Setting up and Configuration of Excel | |||
| copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
![narp[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
