Coloring the bars greater than a certain value in a histogram

Ideally you could have used the ColorFunction option, but sadly for Histogram it seems that it only takes the height as an input. You can hack it by combining two histograms:

With[{d = RandomVariate[NormalDistribution[], 1000]},
 Show[
  Histogram[Select[d, # < 0 &], 
   PlotRange -> {{Min[d], Max[d]}, Automatic}],
  Histogram[Select[d, # >= 0 &], ChartStyle -> Red]
  ]
 ]

A post-processing method: Histogram returns graphics composed of either Tooltip + RectangleBox[] or simple Rectangle[], depending on how many bins. So we need a transformation rule for each.

rectRule[cf_] := r : Rectangle[{x1_, y1_}, {x2_, y2_}, opts___] :> {cf[x1, y1, x2, y2], r};
tooltipRule[cf_] := Tooltip[StatusArea[e_, v_], t_] :> 
   With[{color = cf @@ First@Cases[e, 
         RectangleBox[{x1_, y1_}, {x2_, y2_}, opts___] :> {x1, y1, x2, y2}, Infinity]},
    Tooltip[StatusArea[Prepend[e, color], v], t]
    ];
colorize[cf_] := # /. {rectRule[cf], tooltipRule[cf]} &;

(* user color function: color by z-score *)
cf[x1_, y1_, x2_, y2_] := ColorData[97][Ceiling[Abs[Mean[{x1, x2}]]]];

With[{d = RandomVariate[NormalDistribution[], 1000]}, (* yohbs' example *)
    Histogram[d, PlotRange -> {{Min[d], Max[d]}, Automatic}]
  ] /. {rectRule[cf], tooltipRule[cf]}

Another example, showing individual coloring of bin (according to how far from it expected value each is).

zscore[x1_, x2_, y2_] := With[{p = Probability[x1 < x < x2, 
      x \[Distributed] NormalDistribution[]]},
   (y2 - 1000 p)/StandardDeviation[BinomialDistribution[1000, p]]];
cf[x1_, y1_, x2_, y2_] := 
  ColorData["RedGreenSplit"][Rescale[zscore[x1, x2, y2], {-2.5, 2.5}]];
SeedRandom[2];
With[{d = RandomVariate[NormalDistribution[], 1000], dx = 0.2},
 Legended[
  Show[
   Histogram[d, {dx}, PlotRange -> {{Min[d], Max[d]}, Automatic}] // colorize[cf],
   Plot[dx*1000 PDF[NormalDistribution[], x], {x, -3, 3}],
   PlotLabel -> "Variability in sampling"
   ],
  BarLegend[{"RedGreenSplit", {-2.5, 2.5}}, LegendLabel -> "S.D.", LabelStyle -> "Label"]
  ]]