Copying Conditional formating in a List of text and values.

[Iraj]

I have a list of text and values i.e. A5:M5 and 200 rows beneath. I
highlighted A5:M5>CF>New rule>Use a formula to determine which cells to
format> =$A5$=100%>Format color yellow>ok. When A5 is = 100% the A5:M5 will
be automatically yellow, but when I want to copy this format to the beneath
rows it will change all the original text and values. Is there any rule to
copy this conditional formatting to following 200 rows, where the original
text and values don't change?

With format brush I tried but no result.
With CTRL+D also no result.
I desire those rows which have 100% value in the relevant cell changes the
color not the entire sheet at once.

If there is no other way to fulfill this CF, please instruct me by Macro
VAB. But explain it in detail as I am not professional in Macro.


Thanks Iraj.

 

[Stefi]

Your request is not clear! If I understood it right you want to extend CF to
the following 200 rows. If so, select range A5:M205 then apply CF like you
did it!

Regards,
Stefi

„Iraj†ezt írta:

 

[Iraj]

I have done this before but the entire sheet will change the cell color with
the first cell value when it is =100%. It dosent work at all. I want for
instance if 70 rows have the relevent cell =100% those rows change the color
and the rest will remain normal white color.

Thanks Iraj.

 

[Stefi]

Do you mean that you want to change color of a row if column A in that
particular row is 100%?
Stefi


„Iraj†ezt írta:

 

[Stefi]

Another guess: What is the content of cells in column A and how are they
formatted? If you see 100% in the cell and the cell is formatted as percent,
you have to use formula =$A5=1 in CF.

Stefi



„Stefi†ezt írta:

 

[Iraj]

Imagin column A5:A205 will change with a formated formula related to others
cell values in the same row. Thus when the list items are complitly
purchased, it means that work is complted 100% now A5=100% therefore the
entire A5:M5 will turn to yellow colr due to A5=100%. Similarly it may take
place in some different rows according to complition of work, but not for all
at once or in one day.

Thanks for being so patient. Iraj

 

[Iraj]

# Name Unit Quan-tity Installed %Completed

I II III IV
N1 Inlet Ventilation System.
Air Handling Unit. Which works on Variable rotation speed and which Air
Flow rate is L=21000 m3/h, DP=500 Pa (Static pressure
loss).mixed air,indoor type, Max.Noise Level = 70 dB(A). set 1 0
1.1 Heating Coil Heating capasity N= 200K w, Heating water temperatures =
80/60°C, Air inlet temperature =-8°C, Air outlet temperature = 20°C pcs 1 0
1.1.1. Thee way motorized valve Ø "-" 1 0
1.2 Cooling Coil Cooling capasity =110Kw, Cooling water temperatures =
7/12°C, Air inlet temperature = 38°C, Air outlet temperature = 25°C "-" 1 0
1.2.1 Thee way motorized valve Ø "-" 1 0
1.3 Filter, Filter Class =G4 (panel type), Filter efficiency = 85% "-" 1 0
1.4 Heat Recovery N=90kw ,air - water - air type heat exchanger, Min.
efficiency: 50% "-" 1 0
1,4,3 Circulation Pump For Recuperation L=8.0 m3/h. H=50kpa "-" 1 0
1.5 Silencer (on the supply side) L=0.6m, 250 Hz, sound absorption
20db "-" 1 0
Inlet Ventilation Grilles. L=200 m3/h (200*150) "-" 2 0
Inlet Ventilation Grilles. L=240 m3/h (200*150) "-" 4 0
Inlet Ventilation Grilles. L=250 m3/h (200*150) "-" 30 0
Inlet Ventilation Grilles. L=280 m3/h (200*150) "-" 2 0
Inlet Ventilation Grilles. L=290 m3/h (200*150) "-" 3 0
Inlet Ventilation Grilles. L=300 m3/h (200*150) "-" 4 0
Inlet Ventilation Grilles. L=500 m3/h (300*200) "-" 18 0
Air Flow Dumper. (350*350) "-" 2 0
Air Flow Dumper. (450*350) 1 0
Air Flow Dumper. (450*450) "-" 2 0
Isolation For Galvanized steel duct m² 600 0
Galvanized steel duct d=0,8mm m² 600 0
This is part of my list.

 

[Stefi]

Post the formula in A5:A205!
Stefi


„Iraj†ezt írta:

 

[Stefi]

Not very informative in this form! I can't see how %Completed is calculated!
Another problem that %Completed is not in column A (it's in column F).

Stefi


„Iraj†ezt írta:

 

[Iraj]

I am sorry that I pasted from my Excel sheet but it attached as such. No
difference which column you chose to be =100%. Any cell in one row when is
=100% , then CF for that row will change the row's color to yellow. Now how
to copy this CF through out the following rows where the original text and
values are not changed.

Thanks a lot Iraj.

 

[David Biddulph]

=$A5$=100% is NOT a valid formula. Copy the one which you are actually
using, & paste it here, rather than retyping and getting it wrong.

If you are using =$A$5=100% in the other rows, then you are asking all the
rows to be formatted according to the content of A5.
You perhaps wanted =$A5=100% ?
Remind yourself of the difference between relative and absolute addressing.

 

[Iraj]

The column I want to be =100% is formulated with the values and related to
the quantity column and installed column where these two cells are = then the
completed cell is=100% thus CF will change the whole row's color to yellow.

Thanks Iraj.

 

[Iraj]

Thanks David, but e.g. when M cell is =$A$5:$M$5=100% written in CF the color
changes.

 

[David Biddulph]

I suggest that you read my message again.

 

[Iraj]

Good evening David:
You are correct. I changed the formula and worked perfectly.

Best regards Iraj.