How do I set different colours in geom_errorbarh depending on the value in R

You could create an extra column that gives the colors using case_when depending on the conditions you mentioned. You can use the following code:

library(dplyr)
library(ggplot2)
forestdata %>%
  mutate(color = case_when(average_att > 1 ~ "red",
                           average_att < -1 ~ "blue",
                           TRUE ~ "cyan4")) %>%
  ggplot(aes(average_att, city))+
  geom_errorbarh(aes(xmax =average_att_upper, xmin = average_att_lower, colour = color),size= 1,height = 0.1, show.legend = FALSE) +
  geom_point(size=2,color = "black")+
  scale_x_continuous(limits= c(-6.5, 6.5))+
  geom_vline(aes(xintercept = 0),color="darkgoldenrod2", size = 1,alpha=0.5) +
  scale_color_manual(values = c("red" = "red", "blue" = "blue", "cyan4" = "cyan4"))

^{Created on 2022-08-25 with reprex v2.0.2}

Note: There is no value of "average_att" higher than 1, that's why no red.

More or less same as Quinten, just using factor and scale_color_discrete to keep things in order:

I added a 4th row to the data:

library(tidyverse)

forestdata %>% 
  mutate(color = case_when(average_att > 1 ~ "red",
                           average_att < -1 ~ "blue",
                           TRUE ~ "cyan4")) %>% 
  mutate(color = factor(color, levels =  c("red", "cyan4", "blue"))) %>% 
  ggplot(aes(average_att, city, color = color))+
  geom_errorbarh(aes(xmax =average_att_upper, xmin = average_att_lower),size= 1,height = 0.1)+
  geom_point(size=2,color = "black") +
  scale_x_continuous(limits= c(-6.5, 6.5))+
  scale_color_discrete(breaks = c("blue", "cyan4", "red")) +
  geom_vline(aes(xintercept = 0),color="darkgoldenrod2", size = 1,alpha=0.5)

forestdata <- structure(list(city = c("HK6", "IS2", "CR5", "blabla"), average_att = c(0.752007081, 
-4.384720798, -0.66515768, 3), average_att_lower = c(-1.284027031, 
-6.088057285, -3.094599095, 2), average_att_upper = c(2.073787674, 
-2.637218695, 1.766942459, 4)), row.names = c(NA, 4L), class = "data.frame")