coloring cells and conditional formatting at the same time

I have an excel here.
These are vehicles casanova,gokia,orbit.
Different vehicles have different averages.
So a vehicle in category casanova falls in red zone if its average is between 5-7.
I want to count the number of such vehicles who fall in red category for casanova
and at the same time color them.
I came to know about a formula COUNTIFS(F4:F45,"casanova",F4:F45,">=5",F4:F45,"=<7")
but this kind of formula will just count the vehicles which will fall in category RED for casanova.
I want to color also at the same time.
I have to do this for all the vehicles and each category and multiple ranges.
Here is my excel.

You need to read the help description for COUNTIFS and understand how it works so you can modify it, one formula for each combination of car and count (12 rules).

Also your ranges don't make sense because they overlap. For casanova, is a count of 7 red, or yellow?

For casanova yellow

Formula:

Please Login or Register  to view this content.

For orbit red

Formula:

Please Login or Register  to view this content.

and so forth.

I would put your ranges in two separate cells instead of using "5-7". That way if the ranges change, you don't have to change the formulas. For example put 5 in D48 and 7 in E48, then the formula for casanova red is

Formula:

Please Login or Register  to view this content.

Yes I understand what you say by ranges overlapping. The concerned person whom I asked has little understanding of what I am saying so they do not give clarity of what needs to be done.
My current problem is ,is it possible to color at the same time by some formula the given ranges. So that I select cells in a column and if they meet
some criteria let's say >5 AND <7 then color it red by a formula or script.

Yes. Use Conditional Formatting using formulas as I described.

Your title says "conditional formatting" so I assume you are familiar with it. Do you need detailed instructions for how to implement those formulas in conditional formatting?

Originally Posted by davsth

Unless I am being silly
COUNTIFS(F4:F45,"casanova",F4:F45,">=7",F4:F45,"=<8")

will never happen as the value needs to be Casanova and >=7 and <=8

note I have not opened the spreadsheet as no access to excel on phone

That is a good point that I completely missed. The car names are in column C.