We help IT Professionals succeed at work.

Conditional fromatting formula

101 Views
Last Modified: 2017-04-15
My current formula:  =IF(AND($H4<>"",$I4<>""),J4=MEDIAN(J4,$H4,$I4)) does work;  However, I need to and another condition.  If need to if G4 = inspection turn green, if G4 = training turn red
Comment
Watch Question

RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
you cannot use J4 in this formula J4=MEDIAN(J4,$H4,$I4)
gowflowPartner
CERTIFIED EXPERT

Commented:
could you post a sample workbook as your initial formatting is not clear
gowflow

Author

Commented:
Uploading now...
gowflowPartner
CERTIFIED EXPERT

Commented:
Also your not telling us your conditional formatting apply to what range and cells.
gowflow
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
Try This:
you can also change the colors.
Test.xlsx
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
I Updated the attachment, please try it again.

Author

Commented:
Sorry for the delayed response.  What I'm trying to is have the dates created a Gantt chart but I want it to based upon date range and category.   So each category will be a different color.
125-Maint-Analysis_Events_database-v.xls
gowflowPartner
CERTIFIED EXPERT

Commented:
sorry the file you posted is
125-Maint-Analysis_Events_database-v.xls
However when we open it is a Word document !!! ending with .doc

check what you posted as this is an Excel thread and repost the correct file.

gowflow
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
Create your chart then use Conditional Formating and Manage Rules for those cells.

How to manage Rules:
Select the cell(s) you want to apply the rules.
Select Home tab from above excel.
Click on Conditional Formating and select Manage Rules.
Select New for creating new roules to edit to edit rules.
RaminTechnical Advisor
CERTIFIED EXPERT

Commented:
@Gowflow - if you change the file type to XLS it's OK.

Author

Commented:
Sorry.  I'm working in excel.  So, maybe inadvertently uploaded wrong file.  Here you go...
125-Maint-Analysis_Events_database-.xlsx
gowflowPartner
CERTIFIED EXPERT

Commented:
ok now we are talking. The file is fine and nice by the way !!!
Lets go back to what you want.

When you have training or Inspection in G4 what you want to color ??? you want to add the existing formula ? or simply color the word training and inspection ?

gowlfow
gowflowPartner
CERTIFIED EXPERT

Commented:
Is this what your looking for ? See the attached file.
gowflow
125-Maint-Analysis_Events_database-.xlsx

Author

Commented:
I want to modify or change the existing conditional formatting formula to highlight the corresponding date range with the category.  If you unhide the rows up top you will see the categories or loo at the drop-down tab.  I have seven (7) categories.
gowflowPartner
CERTIFIED EXPERT

Commented:
I see

Just 1 comment:
January is fine but February till December does not have does not have
Details      Venue      Command      Location      Category      Start Date      End Date

Shouldn't you first add them so we fix the whole Workbook in 1 go ?

gowflow

Author

Commented:
If you think that's best.  I was planning on applying the solution to remain spreadsheet.  But ok.
gowflowPartner
CERTIFIED EXPERT

Commented:
don't worry just be patient I am doing all this for you. Will get back as hv to step out for few hours.
gowflow
gowflowPartner
CERTIFIED EXPERT

Commented:
Is this what your looking for ?

I don't understand why but in every row where you have dates the 2 and 3 are colored is this fine ??

I modified the wb to accommodate the formula to include the whole workbook also adjusted the headings for all months and incorporated training and inspection.

Not too sure about what it should color though !! if you can advise then maybe will fix the formula to color the correct range. My feeling is that you want to color the date range is that correct ??

Let me know your comments.
gowflow
125-Maint-Analysis_Events_database-.xlsx

Author

Commented:
The is exactly what I was looking for.  I have attached a spreedsheet with a legend at  the top.  The colors key can be anything, but I would like FHP - Red, UTA- Blue, etc.,
125-Maint-Analysis_Events_database-.xlsx
gowflowPartner
CERTIFIED EXPERT

Commented:
yes but first is the formula ok ? about the 2 and 3 that are being colored is it fine ?
gowflow

Author

Commented:
Yes, exactly what I needed.
Partner
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
yes, but the colors can be whatever...

Author

Commented:
Are the cells highlighting properly.
gowflowPartner
CERTIFIED EXPERT

Commented:
What do you mean by:
colors can be whatever...

and
Are the cells highlighting properly.

Sorry I don't understand what you want and what you are commenting. Please be more explicit.
gowflow

Author

Commented:
Meaning whatever coloring you setup is good with me.  When you enter the dates are highlight cells correctly
gowflowPartner
CERTIFIED EXPERT

Commented:
ok you need now all the items listed in legend to be included in the color formatting ? if yes then I suggest you close this question as it has answered your initial request and post a new question. If you would like my help then please put a link here of the new question and I will be glad to help.

gowflow

Author

Commented:
Thank you.  New question posted.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.