SOLVED

conditional formatting formula help please

Copper Contributor

I need some assistance trying to figure out how to do a conditional formatting based on a partial string of information in a cell.

 

data strings to be conditionally formatteddata strings to be conditionally formatted

 

  This is an image of what I'm working on. I'd like the conditional formatting to find any cells that are 96.00% or higher, AFTER the "/", and then change the cell's color.

 

If you guys know of a formula or formatting that can do that I'd really appreciate the help. I've been racking my brain for almost a month to make this happen.

 

Much gratitude.

7 Replies
best response confirmed by James Cook (Copper Contributor)
Solution

you need to setup your conditional formatting using this formula in the criteria

 

so simply select the column lets say column A and then go to conditional formatting and put this below formula.

 

=((RIGHT(A1,LEN(A1)-FIND("/",A1,1)))+0)>0.96

 

please download and open the attached example which I uploaded here.

 

 

Jamil Mohammad, thank you so much!

This is exactly what I was trying to create, but for some reason my formula was falling apart. This is working perfectly now. I'm so grateful

You are welcome James.

Thanks for the feedback.

Hi

 

Could someone help me with conditional formatting formulas.

 

I have list of tasks in Col B and Tasks status in Col D - I would want to strikeout the Tasks in Col A if Col D task status is marked as "Completed"

 

Could someone help please

 

Regards

Seema

Salaam Seema,

 

please see attached workbook with example.

 

if Column D is "Completed" Column A gets strikeout.

Walaikkumassalaam and Shukriya , this is amazing!!

 

However I still have one doubt , how come the formula used say $D2 - so does that always reference to same field? or does it change to the next row automatically but does not show on the formula?

I used mixed Reference of cell in the formula.
although it shows $D2 but then you can see a dollar sign before D that means Column is blocked but there is no dollar sign before 2 which means row is not blocked and when formula is set like that then the row is relative and in next rows it takes care of the row number automatically.
1 best response

Accepted Solutions
best response confirmed by James Cook (Copper Contributor)
Solution

you need to setup your conditional formatting using this formula in the criteria

 

so simply select the column lets say column A and then go to conditional formatting and put this below formula.

 

=((RIGHT(A1,LEN(A1)-FIND("/",A1,1)))+0)>0.96

 

please download and open the attached example which I uploaded here.

 

 

View solution in original post