Conditional formatting to add text from a cell

[mdmhal]

Hi Dream Team,

Hoping to get some assistance.

I am conditional formatting using the following formula:

=AND($C7="Specialist",J$6>=$E7,J$6<=$F7)

This currently does what I need it to do, with the the relevant highlighted cells turning the right colour based on my customised format.

However I'd like to add one further detail and i'm stuck....

Is there a way to amend my formula, so that the relevant highlighted cells can also contain whatever text is within cell $D7

Any help would be truly appreciated.

All the best,
Hal

 

[Joe4]

Is there a way to amend my formula, so that the relevant highlighted cells can also contain whatever text is within cell $D7

Do you mean that you want to add a condition to check to see if the cell also contains whatever is in cell D7?
If so, then just add another check to your and clause, i.e. if you were checking cell B6, it might look something like:

=AND($C7="Specialist",J$6>=$E7,J$6<=$F7,$B6=$D7)

(depending on whether it needs to be an exact match or not).

If you are asking if you can ADD the value in cell D7 to the value that is returned in the cell, Conditional Formatting cannot do that.
It can only format the cell, not change their contents.
To do something like that would require VBA.
If you would like to pursue that option, please provide more details, maybe an example (so that we know exactly which cell we are adding the value to).

 

[mdmhal]

If you are asking if you can ADD the value in cell D7 to the value that is returned in the cell, Conditional Formatting cannot do that.
It can only format the cell, not change their contents.

Hi Joe,

Ideally and I'm not sure if it is possible, I'm asking to put whatever value number that cell D7 contains into any cells that meet the condition. All the cells that are highlighted for the conditional formatting are other wise empty / hold no value. They are simply shown in the block colour that I have chosen with the customised format.

Hope that makes sense. Is that possible?

Thanks,
Hal

 

[Joe4]

As I said, that is not possible with Conditional Formatting. Conditional Formatting CANNOT change the returned value, it can only change how the cell is formatted.

Are you open to a VBA solution?
If so, please post an example of EXACTLY what you are trying to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mdmhal]

Joe,

Thanks for your help, no problem. I only wanted to check I wasn't missing something obvious.

No dramas I'll live without for now.

Best,
Hal

 

[Joe4]

You are welcome.

Yes, other than manual entry, the only thing that can change the actual contents or formula in a cell is VBA.
Conditional Formatting can only affect the appearance, not the actual cell contents.

Note that if the cells you are talking about contain formulas, we may be able to build another piece of that formula to do what you want, i.e. use an IF function to check the same conditions you are using for your Conditional Formatting, and if they are met, add the value from D7 to the end, else add nothing to the end.