Geek Answers Handbook

How can I copy conditional formatting in Excel 2010 to other cells that is based on different cell contents?

I need to copy conditional formatting based on a formula to other cells, but I need to change the formula for each individual cell state. How can I make a copy of this condition so that the formula also changes? in cells when copying cells with a formula that applies to other cells, then excel will accordingly change the corresponding cells. But I don’t know how to do the same in conditional formatting, so I don’t have to manually change the conditional formula:

condition: = K21 + $ F22 (conditional formula that I put in cell K22) condition: = AB21 + $ F22 (conditional formula that should be in cell AB22)

Since I have more than 10,000 cells for applying the conditional formatting formula, it is practically impossible to enter this formula manually in each individual cell. Is there any way to do this automatically?

I do not know VB, but I can find out if there is only a solution. But please give me the abbreviation and fully explain.

Thank you so much! Shahab

+5
source share
5 answers

, , . , " , , ". , H23 *.25. H24 *.25, H25 *.25 .. , .

+5

" " "" Excel 2010.

, : " , ":

, "" (.. $s), " " , , , , ( ), "".

, .

copy paste-special , .

+2

: = K21 + $F22

. . CONDITION, BOOLEAN (True/False). True, .

CONDITION,

condition: =K21+$F22>0

CF

1) select the entire range that you want the Conditional FORMAT to be applied to.

2) enter the CONDITION, as it relates to the FIRST ROW of your selection.

CF .

+1

! , , , , , . , , , . , , , . , , .

:

A1 ( , ). " " " " . , , :

=A1=A50

A1 A50 , . : $$, ! A1 PasteSpecial (). , , , ( line-YUK!)

, , !

- : ISBLANK, , , , , (.. BLANK).

. !

0

, VBA.

, , .

i.e. Although column F contains the values ​​that I want to format, the desired formatting for each cell is written in column Z, expressed as "RED", "AMBER", or "GREEN."

Quick solution below. Manually select the range in which you want to apply conditional formatting, and then run the macro.

   Sub ConditionalFormatting()
For Each Cell In Selection.Cells
With Cell
    'clean
    .FormatConditions.Delete

    'green rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""GREEN"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -11489280
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False

    'amber rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""AMBER"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249946592608417
    End With
    .FormatConditions(1).StopIfTrue = False

    'red rule
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=$Z" & Cell.Row & "=""RED"""
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .Color = -16776961
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
End With

Next Cell

End Sub
0
source

More articles:


All Articles